Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " wrote: Just to clarify, I'm looking for a UDF that would do that. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " wrote: Just to clarify, I'm looking for a UDF that would do that. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(SE ARCH("berries",A6:A25)),ROW($A6:$A25 )),1),"None") or B1: =IF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MAT CH(TRUE,ISNUMBER(SEARCH("berries",A6:A25)),0))),"N one") 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: |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) =IF(COUNTIF(A6:A25,"berries"),SMALL(IF(ISNUMBER(SE ARCH("berries",A6:A25)),R*OW($A6:$A25 )),1),"None") 2) =IF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MAT CH(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 . Again I very much appreciate your help. Best, Brandon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,M ATCH(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(SE ARCH("berries",A6:A25)),RÂ*OW($A6:$A25 )),1),"None") 2) =IF(COUNTIF(A6:A25,"berries"),ROW(INDEX(A6:A25,MAT CH(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 . Again I very much appreciate your help. Best, Brandon |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Text String that matches with list | Excel Worksheet Functions | |||
how do i find multiple matches of one data item in an excel range | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
backwards find function to find character in a string of text | Excel Programming |