Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
I need a formula that returns the cell location of the first instance
of a text string that I specify in a column listing. I also need a formula that returns the cell location of the last instance of a text string I specify in a column. For example lets say the text I want to look for is 'car', The first formula would return A2 and the other formula would return A5. I hope that makes sense. Can anyone help? A1 A2 cars and trucks A3 vans A4 truck and racecar A5 red car A6 bean A7 rice Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
One way ..
Assuming source data within A2:A7 with the text to search entered in B2: car Then in say, C2, array-entered*: ="A"&MATCH(TRUE,ISNUMBER(SEARCH(B2,A2:A7)),0)+1 returns the 1st instance: A2 And in say, C3, array-entered*: ="A"&MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7 )) returns the last instance: A5 *Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER Replace SEARCH with FIND if you need the search to be case sensitive. SEARCH is not case sensitive. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ps.com... I need a formula that returns the cell location of the first instance of a text string that I specify in a column listing. I also need a formula that returns the cell location of the last instance of a text string I specify in a column. For example lets say the text I want to look for is 'car', The first formula would return A2 and the other formula would return A5. I hope that makes sense. Can anyone help? A1 A2 cars and trucks A3 vans A4 truck and racecar A5 red car A6 bean A7 rice Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
Provided you are using Excel 2000 or later you could use these User
Defined Functions which will recognise either the singular form of the string eg "car" or the singular form with a trailing "s" ie "cars"... Public Function TextStart(Text As String, Range As Range) As String Dim rngCell As Range, arrText() As String For Each rngCell In Range If rngCell.Value < "" Then arrText = Split(rngCell.Value) Select Case arrText(0) Case Text, Text & "s" TextStart = rngCell.Address(False, False) Exit Function End Select End If Next rngCell TextStart = "" End Function Public Function TextEnd(Text As String, Range As Range) As String Dim rngCell As Range, arrText() As String For Each rngCell In Range If rngCell.Value < "" Then arrText = Split(rngCell.Value) Select Case arrText(UBound(arrText)) Case Text, Text & "s" TextEnd = rngCell.Address(False, False) Exit Function End Select End If Next rngCell TextEnd = "" End Function Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
On Feb 17, 2:47 am, "Max" wrote:
One way .. Assuming source data within A2:A7 with the text to search entered in B2: car Then in say, C2, array-entered*: ="A"&MATCH(TRUE,ISNUMBER(SEARCH(B2,A2:A7)),0)+1 returns the 1st instance: A2 And in say, C3, array-entered*: ="A"&MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7 )) returns the last instance: A5 *Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER Replace SEARCH with FIND if you need the search to be case sensitive. SEARCH is not case sensitive. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik wrote in message ps.com... I need a formula that returns the cell location of the first instance of a text string that I specify in a column listing. I also need a formula that returns the cell location of the last instance of a text string I specify in a column. For example lets say the text I want to look for is 'car', The first formula would return A2 and the other formula would return A5. I hope that makes sense. Can anyone help? A1 A2 cars and trucks A3 vans A4 truck and racecar A5 red car A6 bean A7 rice Thanks! Thanks for the responses! Are there similar formulas just to return the row number instead of the the complete cell address? Preferably without using arrays. Thanks again! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
=Match("*"&B2&"*",A2:A7,0)+1
will work for the first value. -- Regards, Tom Ogilvy wrote in message ups.com... On Feb 17, 2:47 am, "Max" wrote: One way .. Assuming source data within A2:A7 with the text to search entered in B2: car Then in say, C2, array-entered*: ="A"&MATCH(TRUE,ISNUMBER(SEARCH(B2,A2:A7)),0)+1 returns the 1st instance: A2 And in say, C3, array-entered*: ="A"&MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7 )) returns the last instance: A5 *Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER Replace SEARCH with FIND if you need the search to be case sensitive. SEARCH is not case sensitive. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik wrote in message ps.com... I need a formula that returns the cell location of the first instance of a text string that I specify in a column listing. I also need a formula that returns the cell location of the last instance of a text string I specify in a column. For example lets say the text I want to look for is 'car', The first formula would return A2 and the other formula would return A5. I hope that makes sense. Can anyone help? A1 A2 cars and trucks A3 vans A4 truck and racecar A5 red car A6 bean A7 rice Thanks! Thanks for the responses! Are there similar formulas just to return the row number instead of the the complete cell address? Preferably without using arrays. Thanks again! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
wrote
.. Are there similar formulas just to return the row number instead of the the complete cell address? Preferably without using arrays. Array-entered in C3: =MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7)) will return the last instance's row number: 5 (Just remove the front part: "A"& ... in the earlier formula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formulas for finding text strings
Amended to return row and extended to deal with "es" plurals eg
volcanovolcanoes and "ves" plurals eg shelfshelves. Words like footfeet are simply too difficult... Public Function TextStartRow(Text As String, Range As Range) Dim rngCell As Range, arrText() As String For Each rngCell In Range If rngCell.Value < "" Then arrText = Split(rngCell.Value) Select Case arrText(0) Case Text, Text & "s", Text & "es", Left(Text, Len(Text) - 1) & "ves" TextStartRow = rngCell.Row Exit Function End Select End If Next rngCell TextStartRow = "" End Function Public Function TextEndRow(Text As String, Range As Range) Dim rngCell As Range, arrText() As String For Each rngCell In Range If rngCell.Value < "" Then arrText = Split(rngCell.Value) Select Case arrText(UBound(arrText)) Case Text, Text & "s", Text & "es", Left(Text, Len(Text) - 1) & "ves" TextEndRow = rngCell.Row Exit Function End Select End If Next rngCell TextEndRow = "" End Function Also, not entered as array formula, simply paste into a code module in that worbook's VBA Editor then enter formula on the worksheet. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas are becoming text strings | Excel Discussion (Misc queries) | |||
Finding the length of proportional text strings | Excel Programming | |||
Finding text strings in complex situations | Excel Discussion (Misc queries) | |||
Finding 13 character codes in text strings | Excel Worksheet Functions | |||
Finding Duplicate text strings with a single column | Excel Worksheet Functions |