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

 

[JMB]

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)

 

[JMB]

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.

 

[Harlan Grove]

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

....

And yet another way involves using a few defined names.

seq: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1))

Then the rightmost position of the substring x in y would be given by

=LOOKUP(2,1/(MID(y,seq,LEN(x))=x),seq)

And if you want to use wildcards,

=LOOKUP(2,1/(SEARCH(x,MID(y,seq,LEN(y)))=1),seq)

Granted this isn't exactly obvious, but it works WITHOUT array entry or
volatile functions. But this is what you get when misusing a program
intended for numeric financial calculations for text manipulation and/or
general list processing.

 

[4mula freak]

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!

 

[4mula freak]

A very intriguing approach. Another day I must try again to implement it.
I know how to name lists or cells, so I think seq: is just one cell, with
the formula you gave. No variable in seq's formula, right? Why 1024?
And I know very well VLOOKUP, sometimes HLOOKUP, but never just LOOKUP.
Interesting function - Vector or Array, MS Help says. INDEX is like that,
too. Which way using LOOKUP here - Vector or Array?
I've used MID a lot, but rarely like this. We're seeking a Boolean
true/false value here (0, or 1), right? (I goofed on prelim effort, it
always comes out #DIV/0! even if x is in y. I must be making an error.)
Thanks for sharing this amazing alternative strategy. It is almost like
using formulas to build other formulas to feed the INDIRECT function. As you
granted, it's not so obvious. I like using higher level controls over lower
constructs.
As to note about oddity of design stemming from misusing what at root is
a financial program, by forcing it to do:

text manipulation and/or general list processing...

Yes, likely some truth to that. I would add there's a long history of doing
non-financial things. Excel predecessors Lotus, Visicalc, all had nearly
every text-oriented command very close to what BASIC had from early days
(Dartmouth). One thing none have is a FOR... NEXT recursion control loop.
None allow "circular ref", not counting something that might be possible
behind the scenes in VBA. Likely this also may be why odd ways to do job,
text or financial, sometimes is sought out.

 

[Harlan Grove]

I know how to name lists or cells, so I think seq: is just one cell, with
the formula you gave. . . .

No, seq is a defined name. Run the menu command Insert > Name > Define,
enter seq as the name, and enter the formula I gave previously in the Refers
to field.

. . . No variable in seq's formula, right? . . .
Correct.

. . . Why 1024? . . .

Just a biggish number. You could make it larger to handle longer strings,
but it's also a trade-off with performance - you don't what to do to much
unnecessary processing.

. . . Which way using LOOKUP here - Vector or Array? . . .

Vector. Count the arguments.

. . . We're seeking a Boolean true/false value here (0, or 1), right?. . .

Not quite. The 2nd argument is 1/(boolean expression), so the denominator is
converted to 1 for TRUE, 0 for FALSE, so the 2nd argument evaluates to 1 for
TRUE and #DIV/0! for FALSE. The largest value in the 2nd argument array is
1, and the 1st argument lookup value is 2. When given a lookup value greater
than any value in its 2nd argument, LOOKUP will always match the *last*
value less than the lookup value.

 

[ilia]

Not sure whether this will help, but you could use this array - {}
brackets mean use Ctrl+Shift+Enter to confirm the formula (don't type
them in):

{=MID(F3,LEN(F3)+1-ROW(INDIRECT("1:"&LEN(F3))),1)}

to return each character as an array element. You can then use MATCH
to find a particular character you're looking for, and use the result
within MID, LEFT, RIGHT, or whatever. For example, the following
formula returns everything after the last semicolon:

{=RIGHT(F3,MATCH(";",MID(F3,LEN(F3)+1-ROW(INDIRECT("1:"&LEN(F3))),1),
0)-1)}

For processing strings, I also like to use SUBSTITUTE to locate a
specific instance of a character in a string. Because you can specify
to substitute only the nth character, you can then use FIND to locate
the position.

As an example, this formula gives you the number of semicolon in the
string:

=LEN(F3)-LEN(SUBSTITUTE(F3,";",""))

This formula will give you the position of the last semicolon in the
string:

=FIND(CHAR(150),SUBSTITUTE(F3,";",CHAR(150),LEN(F3)-
LEN(SUBSTITUTE(F3,";",""))))

This formula will give you everything after the last semicolon:

=MID(F3,FIND(CHAR(150),SUBSTITUTE(F3,";",CHAR(150),LEN(F3)-
LEN(SUBSTITUTE(F3,";",""))))+1,LEN(F3)-
FIND(CHAR(150),SUBSTITUTE(F3,";",CHAR(150),LEN(F3)-
LEN(SUBSTITUTE(F3,";","")))))

You can develop this concept further by using this formula to
determine the position of n-1th instance of ; in F3:

=LEN(F3)-LEN(SUBSTITUTE(F3,";",""))-1

Of course you may have to do some error checking to ensure there are
actually that many semicolons in the string. Since you're using
XL2003, you may also have to split up the formulas into multiple cells
to maintain 7 or fewer nested functions.

 

[Héctor Miguel]

hi, 'anonymous' !

while you could provide a more specific examples -?-
[i'm sure] you will find very helpful tips on David McRitchie's site:
-> Strings and Manipulations
http://www.mvps.org/dmcritchie/excel/strings.htm
http://www.mvps.org/dmcritchie/excel/strings.htm#lastword

hth,
hector.

__ original post __