Search function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

The following function reads from left to right
SEARCH(find_text,within_text,start_num)

Can anyone please help I need the same function but reading from right to
left.

Thanks.
 
nc,

You can use a user-defined-function. Copy the code below, and paste it into
a module in your workbook. Then use it like:

=RSEARCH(find_text,within_text)
=RSEARCH("Bernie","Bernie, you are Bernie")
=RSEARCH(A2, A1)

And, if you don't like UDF's then you can use the array function (entered
with Ctrl-Shift-Enter)

=MAX(IF(ISERROR(SEARCH(A2,A1,ROW(INDIRECT("A1:A"&LEN(A1))))),0,SEARCH(A2,A1,
ROW(INDIRECT("A1:A"&LEN(A1))))))

where A1 is the string to be searched, and A2 is the string to be found, for
both of the above cases.

HTH,
Bernie
MS Excel MVP


Public Function RSearch(strFindText As String, _
strWithinText As String) As Integer
Dim i As Integer

For i = Len(strWithinText) To 1 Step -1
If InStr(i, strWithinText, strFindText) > 0 Then
RSearch = i
Exit Function
End If
Next i

End Function
 
And note, if you are adapting this for other cells, only change the direct
cell references: the A2, A1, and LEN(A1) parts. Leave the "A1:A" parts in
the INDIRECT function alone.

HTH,
Bernie
MS Excel MVP
 
with the help of a simple user-defined function

=find("x",RightToLeft("Excel") = 4

Function RightToLeft(strRTL As String) As String
RightToLeft = StrReverse(strRTL)
End Function
 
nc,

When I tested it, it returned the starting position of the last instance of
the string I was searching for. Perhaps you could post the values that you
are passing to the function(s)?

HTH,
Bernie
MS Excel MVP
 
Bernie

A1=excel
B1=RSearch("x",A1)

the value the function is returning is 2, I was expecting 4.
 
nc,

Oh, I thought you wanted to find the rightmost value:
=RSearch("x","Exactly exxxxxxxtincxxxt")
to return the position of the last x.

For your actaul problem, simply use
=LEN(A1) - SEARCH("x",A1) +1

Forget all that other stuff....

HTH,
Bernie
MS Excel MVP
 
Back
Top