On Mon, 20 Aug 2007 18:22:02 -0700, 4mula_freak <4mula
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 maybe one suffix. FIND's Left to right scan is messier to
wade through all variations of first, middle (or not), and/or last name
prefix, if any.
A work-around is possible but very bulky. I use a formula to create my
own reversed text. Then use normal left-to-right FIND on it, and offset
FIND's result against LENgth of forward text field to get desired answer.
How bulky: For a 30-byte name field, you'd need a reversing formula 641
bytes long, with 30 terms in it: =mid(F3,len(F3)-0,1)&mid(F3,len(F3)-1,1)&...
&mid(F3,len(F3)-29,1). (I actually use another formula to build this one.)
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, or isolating patterns for
fraud forensics, etc.



You could use a UDF to reverse the string, at least in later versions (2000+)
of Excel.

==========================
Function Reverse(str As String) As String
Reverse = StrReverse(str)
End Function
===========================

InStrRev is also a function in Excel VBA.

For even more flexibility, you could use a UDF implementing Regular Expressions
(Microsoft VBScript Regular Expressions 5.5).

Or you could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr However, some of the add-in functions won't work in
versions later than Excel 2003 (I'm not sure about the Regex functions) and it
won't handle strings greater than 255 characters in length.

One of the Regular Expression solutions makes parsing a text string a fairly
simple task.

Let me know which you want more information about.
--ron