Need formulas for finding text strings

[bird_222]

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!

 

[Max]

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.

 

[Ken Johnson]

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

 

[bird_222]

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.

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!

 

[Tom Ogilvy]

=Match("*"&B2&"*",A2:A7,0)+1

will work for the first value.

 

[Max]

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

 

[Ken Johnson]

Amended to return row and extended to deal with "es" plurals eg
volcano>volcanoes and "ves" plurals eg shelf>shelves. Words like
foot>feet 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