+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    samer.kurdi@gmail.com
    Guest

    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


  2. #2
    samer.kurdi@gmail.com
    Guest

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

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


  3. #3
    Ron Coderre
    Guest

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

    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.
    >
    >


  4. #4
    samer.kurdi@gmail.com
    Guest

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

    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.
    > >
    > >



  5. #5
    Cornell1992
    Guest

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

    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


  6. #6
    Ron Coderre
    Guest

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

    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
    >
    >


  7. #7
    Cornell1992
    Guest

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

    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


  8. #8
    Ron Coderre
    Guest

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

    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
    >
    >


  9. #9
    Cornell1992
    Guest

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

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1