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