Search function

N

nc

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

Bernie Deitrick

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
 
B

Bernie Deitrick

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
 
D

Duke Carey

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
 
B

Bernie Deitrick

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
 
N

nc

Bernie

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

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

Bernie Deitrick

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
 
Top