formulas exact match

tausif82 · Jul 17th 2006

Hi,

I need to search for an exact match within a cell & i am unsure of how to go abt it ...
Eg ) Cell A1 contains "Emea West,Emea East,Emea". I want to search for the term "Emea" in the contents of the Cell A1.
I cant use Find() as using "=FIND("Emea",A6)" returns 1 ... But i want an exact match i.e "Emea" only which in this case should be 21.
The Exact() works well if Cell A1 had a single value ...I guess I would need to use some array function here which will parse through contents of the cell , but am not sure on how to go about ... Please help !!

Anonymous · Jul 17th 2006

Re: formulas exact match

Depending on the rules that can be supplied, this could be done using FIND and REPLACE. Can you supply the possible positions of the strings you are searching for, and the possible similar strings that must be ignored?

Wigi · Jul 17th 2006

Re: formulas exact match

Perhaps using Data > Text to columns at beginning, with , as separator, might be much easier...

Wigi

austenr · Jul 17th 2006

Re: formulas exact match

Code

Public Function FindPart(ByRef WhatPart As String, Optional LookInRange As Range) As String 
     
    If LookInRange Is Nothing Then Set LookInRange = ActiveSheet.Cells 
    Dim Target As Range, FirstAddress As String 
    With LookInRange 
        Set Target = .Find(WhatPart, LookIn:=xlValues, SearchOrder:=xlByRows, _ 
        LookAt:=xlPart, MatchCase:=False) 
        If Not Target Is Nothing Then 
            FirstAddress = Target.Address '<< bookmark the start-point of the search
            Do 
                If Target Is Nothing Or Target.Address = FirstAddress Then 
                    FindPart = FindPart & Target.Address 
                Else 
                    FindPart = FindPart & ", " & Target.Address 
                End If 
                Set Target = .FindNext(Target) 
            Loop Until Target Is Nothing Or Target.Address = FirstAddress 
        End If 
    End With 
    Set LookInRange = Nothing 
    Set Target = Nothing 
     
End Function

Display More

**Batman** · Jul 17th 2006

Re: formulas exact match

The text you don't want to find appears to be always followed by a space, whereas the text you want to find isn't. Therefore, if you replace Emea followed by a space with different characters then find Emea you should get the right answer:

=FIND("Emea",SUBSTITUTE(A1,"Emea ","#####"))

tausif82 · Jul 18th 2006

Re: formulas exact match

All ...
Thank you very much for your suggestions ...I needed to do find using an excel formula only , so couldnt use text-> columns method/Vba.
many thanks again Batman, the formula you have used seems to be the one I shall be using/tweak around with ! Cheers !

formulas exact match

Participate now!

Share