EXCEL: How to scan text reversed (like ACCESS: InStrRev)?

[4mula_freak]

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.

 

[Ron Rosenfeld]

On Mon, 20 Aug 2007 18:22:02 -0700, 4mula_freak <4mula

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

 

[4mula freak]

I like the simplicity of the 3 line UDF you suggested. Another respondent did
too (JMB). I didn't know about StrReverse() in VBA. But I knew about
InStrRev() in ACCESS database -- it's a directly usable in-formula function
for any ACCESS query. I've not set up a UDF before, but I'll try the HELP
hints on that first.
Thanks for your quick response, Ron! ...Steve

 

[Ron Rosenfeld]

I like the simplicity of the 3 line UDF you suggested. Another respondent did
too (JMB). I didn't know about StrReverse() in VBA. But I knew about
InStrRev() in ACCESS database -- it's a directly usable in-formula function
for any ACCESS query. I've not set up a UDF before, but I'll try the HELP
hints on that first.
Thanks for your quick response, Ron! ...Steve

Steve,

To set up a UDF:

<alt-F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code into the window that opens.

To use the UDF, in some cell on your worksheet, merely enter =Reverse(cell_ref)
(Or =Reverse(string))

To use the UDF in multiple workbooks, I save it as an add-in (.xla) and then
enable that add-in in Excel.

Enjoy.

--ron