Thanks for your followup comment on Ron's use of StrReverse, which I was not
aware of either. Based on your 2nd reply, I'll try simplest first: the 3
line UDF that Ron illustrated on my other post. Sorry about unintended
double-post. I thought my first post got lost when no "Got it" message was
forthcoming. I drafted a 2nd post, but afterward, I see it wasn't needed.
It was my first post ever in the Community. Responses were just great!

"JMB" wrote:

From Ron's response at your other post, I see VBA has a built in function to
reverse a string (StrReverse) - a better solution than the udf I offered.


"JMB" wrote:

You could try a custom function and use VBA's InStrRev function:

Option Explicit
Function FindRev( _
strcheck As String, _
strMatch As String, _
Optional lngStart As Long = -1, _
Optional lngCompare As Long = vbBinaryCompare) As Long
FindRev = InStrRev(strcheck, strMatch, lngStart, lngCompare)
End Function

Check VBA help for InStrRev for information on the required arguments.
syntax is =FindRev(A1, " ")


If you need to reverse the text, you could try another udf for that as well:

Function BackWards(strText As String) As String
On Error Resume Next
BackWards = Right(strText, 1) & BackWards(Left(strText, Len(strText) - 1))
End Function

=Backwards(A1)



"4mula_freak" wrote:

Immediate Purpose: To do a right-to-left scan in EXCEL formula operating
on another text field, similar to what's allowed in ACCESS query formula with
InStrRev function. Why? Often for text fields, like people names, streets,
etc., parsing is easier if can scan text reversed. More quickly isolates
last name root, or one of a handfull of well-known suffixes (Jr, II, III
etc). FIND's left-to-right scan is messier to wade through all first,
middle, and/or last prefix name variations.
A work-around is bulky: Make your own reversed text, then use normal
left-to-right FIND and offsset this against LENgth to get answer. For a
30-byte name field in F3, would need 30 iterations in E3 of:
=mid(F3,len(F3)-0,1)&mid(F3,len(F3)-1,1)&mid(F3,len(F3)-2,1)&...&mid(F3,len(F3)-30,1)
or 641 byte formula. If this was in E3, then answer formula in D3 is:
=(MID(F3,LEN(F3)-FIND(" ",E3,1)+2,999)).
General Purpose: Chop up data easiest way possible to separately field
portions of it. Important in data acquisition and text data analysis.
Examples are parsing raw files to load databases, feed form letters, or
isolate name-patterns for fraud forensics, following the audit trail, etc.
Version of Excel: MSOffice Professional Excel 2003, SP2