Function to return row number where text is in the row

[crimsoneaglet]

My questions centers around whether there is a formula which will enable me to locate a specific text string anywhere in an excel worksheet and return the row number on which it is located.

Essentially at present, I am having to use the ctrl+F function to manually look up each text string, then input some corresponding figures which will be located on the row.

Example of the issue below:

Example reference

APN0000001

What I want the formula to do is:

--> Check if the reference above appears anywhere in a worksheet (may be part of a longer text strong within a cell (e.g. Greater Manchester APN0000001-FRT Create))
--> If it does tell me the row number upon which it appears
--> In an ideal world I'd also like another formula which indicates the number of times the above reference appears in a sheet as well, to ensure that if there is more than one row with the reference in I can combine the relevant figures.

I am keen to use a formula as opposed to vba if possible, if it's not then a vba solution would be greatly appreciated.

Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

Here's one way:

Excel 2010

	A	B	C	D	E	F	G
1	x	2	6
2							zzxaa
3
4			qx
5
6
7				x			x
8
9
10					x
11
12
13							x

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula B1 {=MIN(IF(ISERR(FIND(A1,$A$2:$H$20)),9999,ROW($A$2:$H$20)))} C1 {=SUM(IF(ISERR(FIND(A1,$A$2:$H$20)),0,1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Change the A2:H20 range to match the range where you want to look for the text string. It will return 9999 if it is not found (if you have more rows than that, you'll need to change that too). Use SEARCH if you want it to be case-insensitive.

 

[crimsoneaglet]

Work perfectly, thank you.

 

[Eric W]

Happy to help! Thanks for the feedback.