Hi there,
I have a list of data around 2000 rows long in column A. I'd like to search through the list and when a record contains specific text I'd like the formula to return that row number (ideally the cell reference) so that I can perform additional manipulation of the text in the cell.
In the list of data there will be only around 4 records that have the string, but I need a formula/series of formulas that won't just stop at the first instance.
For testing purposes I've tried this {=SMALL(IF(A:A=F1,ROW(A:A)),ROW(1:1))} where F1 holds the exact value and it returns the row correctly. I then have 3 more rows with Row 2:2 etc on the end.
However I really need something like this {=SMALL(IF(A:A="*string*",ROW(A:A)),ROW(1:1))} where I can enter the string/text into the formula with a wild card on each end, but it just won't work!
Any ideas?
Regards,
Phil
I have a list of data around 2000 rows long in column A. I'd like to search through the list and when a record contains specific text I'd like the formula to return that row number (ideally the cell reference) so that I can perform additional manipulation of the text in the cell.
In the list of data there will be only around 4 records that have the string, but I need a formula/series of formulas that won't just stop at the first instance.
For testing purposes I've tried this {=SMALL(IF(A:A=F1,ROW(A:A)),ROW(1:1))} where F1 holds the exact value and it returns the row correctly. I then have 3 more rows with Row 2:2 etc on the end.
However I really need something like this {=SMALL(IF(A:A="*string*",ROW(A:A)),ROW(1:1))} where I can enter the string/text into the formula with a wild card on each end, but it just won't work!
Any ideas?
Regards,
Phil