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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Mon, 20 Aug 2007 18:22:02 -0700, 4mula_freak <4mula
freak@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: Regular Expression: Not This String And Not That String
    ... no lookahead, and then accept all string that do NOT match. ... These regular expressions are going into a large table of regular ... would require another column in the table (True to reverse the return ...
    (microsoft.public.scripting.vbscript)
  • Re: LED Shorted??
    ... where human factors and lack of reverse ... I've associated end of life or infant mortality of LEDs ... So you have the current through string and voltage across the string? ... at least I put one there so the inductor doesn't kill itself. ...
    (sci.electronics.design)
  • Re: Reverse words in a string (Another Interview question)
    ... >> I'm using a linked list to contain words that will be output in reverse ... A stack is exactly that. ... C's string handling sucks. ... > void reverse_string(char * ostr) ...
    (comp.programming)
  • Re: LED Shorted??
    ... where human factors and lack of reverse ... I've associated end of life or infant mortality of LEDs ... So you have the current through string and voltage across the string? ...
    (sci.electronics.design)
  • Re: A function of reverse complement
    ... I see how you reverse the strand. ... I do not understand how you want to complement the strand. ... Function Rev(Forw As String) As String ... >> Dim strReverseSeq As String ...
    (microsoft.public.excel.programming)