formula to find text and return its cell address

[adhynagara]

Hello... Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this? Thanx in advance!

 

[DeusXv]

Two ways:

1) Formula:

Excel 2007
	A	B	C
1	Hello		$A$1
Sheet1

Cell Formulas
Range	Formula
C1	=ADDRESS(MATCH("Hello*",$A$1:$A$99,0),1)

2) VBA:

Sub FindText()
     
    Dim rngX As Range
     
    Set rngX = Worksheets("Sheet1").Range("A1:A10000").Find("Hello", lookat:=xlPart)
    If Not rngX Is Nothing Then
        MsgBox "Found at " & rngX.Address
    End If
     
End Sub

Hope this helps

 

[adhynagara]

Thank you, DeusXv... But what if I don't know yet the column of the cell contains the text. Your formula uses column A (row 1-99) as the look-up array. On my case, the column could be column A, or B, or may be P, AA, CG, and so on...

 

[DeusXv]

Just alter the ranges in the formula/code so that they now read:

A1:CG100

And the code will search all the cells within that range for that piece of text.

I will also try add a line to the code so that maybe you can enter the range you want in a text box so that you can manually select the ranges and don't have to always edit the VBA code.

Hope this helps

 

[DeusXv]

For entering the range using an input box use this code

Sub FindText2()


    Dim rngX As Range
    Dim MySelection As Range
        Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
        Set rngX = MySelection.Find("Hello", lookat:=xlPart)
            If Not rngX Is Nothing Then
                MsgBox "Found at: " & rngX.Address
            End If
    
End Sub

 

[adhynagara]

Thank you DeusXv... But I'm sorry that altering the ranges into multiple columns and row doesn't work on the Match formula. It returns #N/A error. Meanwhile, I prefer not to use any VBA code, because the spreadsheet I'm working on is very mobile, and it will be hard to transfer any code with any mobile files...

 

[DeusXv]

Yeah I think in a situation like that VBA is probably the only solution as a formula is very one dimensional where as the code is able to adapt and you could just save the code as an "Add-In" so that you can add it to your Add-Ins folder and then just call it from the macro tab this way the code won't be attached to the individual sheet but instead your copy of excel and as an add-in the code can be distributed fairly easily.

Hope this helps

 

[AbhinavC]

Hi,

I have a similar problem however I have the same text 'ab' appear multiple times in the column..eg :

a1 ab
a2 cd
a3 ef
a4 gh
a5 ij
a6 kl
a7 ab
a8 ab
a9 mn

when i use ADDRESS(MATCH("ab",$A$1:$A$9,0),1) i keep getting the first encountered address which is a1, whereas I'd like to see a1, a7, a8..please help

Thanks,
Abhinav

 

[zoraxe]

@AbhinavC
Why don't you use the next blank column for formula that checks if the previous field in the same row has typed "ab" and if it does you get the address..

 

[AbhinavC]

not sure what does that imply, could you type me a formula if you have one in mind ?