Find text in a string that matches value(s) in a range

I have a list of codes that look like this:

cjm-dtm_6a184
342_cjm-fmu323_engine
etc.

I would like a simple formula that would check to see if a string, in
this case "cjm-" exists in the code. Fairly easy to do using the "find"
function, but I'm running into the following problem:

My list of strings-to-look-for has grown such that I am unable to nest
enough "IF" statements within the function to check for all of them.

Does anybody know of the best way to write a macro that would check if
a cell value includes values from a range? Ideally the output would be
the first string found from the left side of the code.

Thanks

Just to clarify, I'm looking for a UDF that would do that.

See if this does what you want:

This function accepts 2 arguments.
1)The first one is a single cell to be tested
2)The second one is a one-dimensional range of cells (in a row or col)
containing values to be sought in the first argument.
It returns the matched text that is located earliest in the test cell

'----Start of Code-----
Option Explicit

Public Function ShowMatch(CellRef As Range, SrchVals As Range) As String

Dim iCtr As Integer
Dim cCell As Range
Dim strTestVal As String
Dim lngMatchRef As Long
Dim Bullpen As String
Dim varFindRef As Variant

'Check if target range is only one cell and has contents
If CellRef.Cells.Count > 1 Then
ShowMatch = "#InvalidCellRef"
Exit Function
ElseIf Len(CellRef) = 0 Then
'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
ShowMatch = "#NoMatch"
Exit Function
End If

'Check if the list to be matched is one dimensional
If SrchVals.Areas.Count > 1 Then
ShowMatch = "#TooManyListRngs!"
Exit Function
ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
'Range is one-dimensional
Else
ShowMatch = "#ListRngNot1Dim!"
Exit Function
End If

lngMatchRef = 99999

'Loop through list values
For Each cCell In SrchVals.Cells
strTestVal = CStr(cCell.Value)

If Len(strTestVal) <> 0 Then
'List ref cell is not blank, so test the cell
varFindRef = InStr(1, CellRef, strTestVal)
If varFindRef > 0 Then
'The list value was found...check it's position in the cell
If varFindRef < lngMatchRef Then
'Use this list value as the first matched value
lngMatchRef = varFindRef
Bullpen = strTestVal
End If
End If
End If
Next cCell
If lngMatchRef = 99999 Then
ShowMatch = "No Match"
Else
ShowMatch = Bullpen
End If
End Function
'----End of Code-----

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"samer.kurdi@gmail.com" wrote:

> Just to clarify, I'm looking for a UDF that would do that.
>
>

Ron,
The code is beautiful, YOU are beautiful. Thank you thank you!


Ron Coderre wrote:
> See if this does what you want:
>
> This function accepts 2 arguments.
> 1)The first one is a single cell to be tested
> 2)The second one is a one-dimensional range of cells (in a row or col)
> containing values to be sought in the first argument.
> It returns the matched text that is located earliest in the test cell
>
> '----Start of Code-----
> Option Explicit
>
> Public Function ShowMatch(CellRef As Range, SrchVals As Range) As String
>
> Dim iCtr As Integer
> Dim cCell As Range
> Dim strTestVal As String
> Dim lngMatchRef As Long
> Dim Bullpen As String
> Dim varFindRef As Variant
>
> 'Check if target range is only one cell and has contents
> If CellRef.Cells.Count > 1 Then
> ShowMatch = "#InvalidCellRef"
> Exit Function
> ElseIf Len(CellRef) = 0 Then
> 'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
> ShowMatch = "#NoMatch"
> Exit Function
> End If
>
> 'Check if the list to be matched is one dimensional
> If SrchVals.Areas.Count > 1 Then
> ShowMatch = "#TooManyListRngs!"
> Exit Function
> ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
> 'Range is one-dimensional
> Else
> ShowMatch = "#ListRngNot1Dim!"
> Exit Function
> End If
>
> lngMatchRef = 99999
>
> 'Loop through list values
> For Each cCell In SrchVals.Cells
> strTestVal = CStr(cCell.Value)
>
> If Len(strTestVal) <> 0 Then
> 'List ref cell is not blank, so test the cell
> varFindRef = InStr(1, CellRef, strTestVal)
> If varFindRef > 0 Then
> 'The list value was found...check it's position in the cell
> If varFindRef < lngMatchRef Then
> 'Use this list value as the first matched value
> lngMatchRef = varFindRef
> Bullpen = strTestVal
> End If
> End If
> End If
> Next cCell
> If lngMatchRef = 99999 Then
> ShowMatch = "No Match"
> Else
> ShowMatch = Bullpen
> End If
> End Function
> '----End of Code-----
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "samer.kurdi@gmail.com" wrote:
>
> > Just to clarify, I'm looking for a UDF that would do that.
> >
> >

Hi Ron,

I saw you recent code posting to Samer on Goolges'
microsoft.public.excel.programming group which Find text in a string
that matches value(s) in a range and if it does find something then it
puts the text string in the cell and if it doesn't then it puts "No
Match". The code was very good.

However, is it possible to modify it to show the position of the text
string in the range of cells. For instance let say I have the following
text starting in range A1:A5

Yellow Orange
Green Grape
Blue Berries
Red Apple
Orange Grapefruit

I find to find the position of the text string "Berries". in the Range
A1:A5. The result I am looking for should produce 3, as it is located
in teh 3rd row in the range. I do not care about the position of the
actual text "Berries" in the entire string "Blue Berries" just the
position of the in the range.

How would I modify the code below to do this?

'----Start of Code-----
Option Explicit


Public Function ShowMatch(CellRef As Range, SrchVals As Range) As
String


Dim iCtr As Integer
Dim cCell As Range
Dim strTestVal As String
Dim lngMatchRef As Long
Dim Bullpen As String
Dim varFindRef As Variant


'Check if target range is only one cell and has contents
If CellRef.Cells.Count > 1 Then
ShowMatch = "#InvalidCellRef"
Exit Function
ElseIf Len(CellRef) = 0 Then
'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
ShowMatch = "#NoMatch"
Exit Function
End If


'Check if the list to be matched is one dimensional
If SrchVals.Areas.Count > 1 Then
ShowMatch = "#TooManyListRngs!"
Exit Function
ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
'Range is one-dimensional
Else
ShowMatch = "#ListRngNot1Dim!"
Exit Function
End If


lngMatchRef = 99999


'Loop through list values
For Each cCell In SrchVals.Cells
strTestVal = CStr(cCell.Value)


If Len(strTestVal) <> 0 Then
'List ref cell is not blank, so test the cell
varFindRef = InStr(1, CellRef, strTestVal)
If varFindRef > 0 Then
'The list value was found...check it's position in the cell
If varFindRef < lngMatchRef Then
'Use this list value as the first matched value
lngMatchRef = varFindRef
Bullpen = strTestVal
End If
End If
End If
Next cCell
If lngMatchRef = 99999 Then
ShowMatch = "No Match"
Else
ShowMatch = Bullpen
End If
End Function
'----End of Code-----


I would greatly appreciate your help, I am not that good at VBA and
this could save me hundreds of hours of manual data manipulation.

Regards,


Brandon
Email: CORNELL1992@AOL.COM

Are you intent on having a VBA solution if you may not need one?

Either of these array formulas returns the row number that contains
specified text:

For the row of the cell in A6:A21 that contains the word "berries":

B1:
=IF(COUNTIF(A6:A25,"berries"),SMALL(IF(ISNUMBER(SEARCH("berries",A6:A25)),ROW($A6:$A25 )),1),"None")

or

B1:
=IF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MATCH(TRUE,ISNUMBER(SEARCH("berries",A6:A25)),0))),"None")

Note: To commit those array formulas hold down the [Ctrl][Shift] keys and
press [Enter].

Something you can work with or do you really require a User Defined Function?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Cornell1992" wrote:

> Hi Ron,
>
> I saw you recent code posting to Samer on Goolges'
> microsoft.public.excel.programming group which Find text in a string
> that matches value(s) in a range and if it does find something then it
> puts the text string in the cell and if it doesn't then it puts "No
> Match". The code was very good.
>
> However, is it possible to modify it to show the position of the text
> string in the range of cells. For instance let say I have the following
> text starting in range A1:A5
>
> Yellow Orange
> Green Grape
> Blue Berries
> Red Apple
> Orange Grapefruit
>
> I find to find the position of the text string "Berries". in the Range
> A1:A5. The result I am looking for should produce 3, as it is located
> in teh 3rd row in the range. I do not care about the position of the
> actual text "Berries" in the entire string "Blue Berries" just the
> position of the in the range.
>
> How would I modify the code below to do this?
>
> '----Start of Code-----
> Option Explicit
>
>
> Public Function ShowMatch(CellRef As Range, SrchVals As Range) As
> String
>
>
> Dim iCtr As Integer
> Dim cCell As Range
> Dim strTestVal As String
> Dim lngMatchRef As Long
> Dim Bullpen As String
> Dim varFindRef As Variant
>
>
> 'Check if target range is only one cell and has contents
> If CellRef.Cells.Count > 1 Then
> ShowMatch = "#InvalidCellRef"
> Exit Function
> ElseIf Len(CellRef) = 0 Then
> 'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
> ShowMatch = "#NoMatch"
> Exit Function
> End If
>
>
> 'Check if the list to be matched is one dimensional
> If SrchVals.Areas.Count > 1 Then
> ShowMatch = "#TooManyListRngs!"
> Exit Function
> ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
> 'Range is one-dimensional
> Else
> ShowMatch = "#ListRngNot1Dim!"
> Exit Function
> End If
>
>
> lngMatchRef = 99999
>
>
> 'Loop through list values
> For Each cCell In SrchVals.Cells
> strTestVal = CStr(cCell.Value)
>
>
> If Len(strTestVal) <> 0 Then
> 'List ref cell is not blank, so test the cell
> varFindRef = InStr(1, CellRef, strTestVal)
> If varFindRef > 0 Then
> 'The list value was found...check it's position in the cell
> If varFindRef < lngMatchRef Then
> 'Use this list value as the first matched value
> lngMatchRef = varFindRef
> Bullpen = strTestVal
> End If
> End If
> End If
> Next cCell
> If lngMatchRef = 99999 Then
> ShowMatch = "No Match"
> Else
> ShowMatch = Bullpen
> End If
> End Function
> '----End of Code-----
>
>
> I would greatly appreciate your help, I am not that good at VBA and
> this could save me hundreds of hours of manual data manipulation.
>
> Regards,
>
>
> Brandon
> Email: CORNELL1992@AOL.COM
>
>

Hi Ron,
Thanks for getting back to me, I very much appreciate your taking the
time to help. I would prefer to use a VBA function to do this that I
could keep in my personal library as the array formulas you presented
can get a bit confusing.

The two formulas you listed are below:
1)
=3DIF(COUNTIF(A6:A25,"berries"),SMALL(IF(ISNUMBER(SEARCH("berries",A6:A25))=
,R=ADOW($A6:$A25
)),1),"None")
2)
=3DIF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MATCH(TRUE,ISNUMBER(SEARCH=
("=ADberries",A6:A25)),0))),"None")


I tried the two array formulas that you listed in a more comlicated
example then the one I provided yesterday and had mixed results. The
first formula worked for about 30% of the cases, I am not sure why did
not work in the rest. The second formula returned #N/A.

In my simplifed example from the previous message I am looking to find
the word Position in the Range of "Berries" from the data below which
is in cells A1:A5
Yellow Orange
Green Grape
Blue Berries
Red Apple
Orange Grapefruit

The word "Berries" appears in the third row in the text "Blue Berries".
I need the function or array formula to be able to pick out "Berries"
from the entire text string "Blue Berries" and ignore the "Blue " part
of the phrase. Each row in the range could have several words such as
"Large Blue Berries" or ""Perfect Blue Berries". The array formula#1
listed above only seemd to find the position of "Berries" or whatever
else I was searching for only when the information appeared alone with
no other text or spaces or symbols in that range. Thus, I thought there
might be away to modify the VBA code you provided to Samer to
accomplish this easily. If you like I could send you the more
complicated example to show you the results with you two different
array techniques. My email is CORNELL1992@AOL.COM.

Again I very much appreciate your help.

Best,

Brandon

First, amendments to the formulas to put wildcards on either side of "berries":

These formulas return the row the contains the criteria text:
=IF(COUNTIF(A6:A25,"*berries*"),SMALL(IF(ISNUMBER(SEARCH("berries",A6:A25)),ROW($A6:$A25)),1),"None")
or
=IF(COUNTIF(A6:A25,"*berries*"),ROW(INDEX(A6:A25,MATCH(TRUE,ISNUMBER(SEARCH("berries",A6:A25)),0))),"None")

Note1: Commit those array formulas by pressing Ctrl+Shift+Enter
Note2: In case window wrap problems occur, there are NO spaces in either
formula.

Next, I need a clarification. The original code I wrote checks if a single
cell contains any of a list of words and returns the first word that was
found. It seems
like you are asking for a formula that looks in a range of cells for any
instance of one specific text and returns the row reference of the first cell
where it is found. The above formulas do that.

Am I missing something? Are you looking for something else?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Cornell1992" wrote:

> Hi Ron,
> Thanks for getting back to me, I very much appreciate your taking the
> time to help. I would prefer to use a VBA function to do this that I
> could keep in my personal library as the array formulas you presented
> can get a bit confusing.
>
> The two formulas you listed are below:
> 1)
> =IF(COUNTIF(A6:A25,"berries"),SMALL(IF(ISNUMBER(SEARCH("berries",A6:A25)),RÂ*OW($A6:$A25
> )),1),"None")
> 2)
> =IF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MATCH(TRUE,ISNUMBER(SEARCH("Â*berries",A6:A25)),0))),"None")
>
>
> I tried the two array formulas that you listed in a more comlicated
> example then the one I provided yesterday and had mixed results. The
> first formula worked for about 30% of the cases, I am not sure why did
> not work in the rest. The second formula returned #N/A.
>
> In my simplifed example from the previous message I am looking to find
> the word Position in the Range of "Berries" from the data below which
> is in cells A1:A5
> Yellow Orange
> Green Grape
> Blue Berries
> Red Apple
> Orange Grapefruit
>
> The word "Berries" appears in the third row in the text "Blue Berries".
> I need the function or array formula to be able to pick out "Berries"
> from the entire text string "Blue Berries" and ignore the "Blue " part
> of the phrase. Each row in the range could have several words such as
> "Large Blue Berries" or ""Perfect Blue Berries". The array formula#1
> listed above only seemd to find the position of "Berries" or whatever
> else I was searching for only when the information appeared alone with
> no other text or spaces or symbols in that range. Thus, I thought there
> might be away to modify the VBA code you provided to Samer to
> accomplish this easily. If you like I could send you the more
> complicated example to show you the results with you two different
> array techniques. My email is CORNELL1992@AOL.COM.
>
> Again I very much appreciate your help.
>
> Best,
>
> Brandon
>
>

Hi Ron,

Thanks again! I re-ran the code with the wildcard "*Berries*" and it
seems to work fine in both methods. I appreciate the help. Also, I did
coomit the formulas to an Array by choosing Ctrol+Shift_Enter. Your
request for clarification was correct. Your formulas do exactly what I
need done.

The only question is about practical use. Is there any way to modify
the originaly code you gave Samer to do the same thing that these two
array formulas do. I will need to send my analysis work onwards to
others an in the past Arrays have been very difficult for others to
understand that are not as technical as you and I, and have gotten
messed up,. That is why I thought a function coded in Excel VBA would
be the better approach. I could insert the function into each
spreadsheet and thus it would flow seemlessly. The function would allow
you to search for "Berries" in a partiicular range and show you its
position. I can be reached at CORNELL1992 at AOL.COM

Regards,

Brandon