Re: EXCEL: How to scan text reversed (like ACCESS: InStrRev)?
- From: ilia <iasafiev@xxxxxxxxx>
- Date: Tue, 21 Aug 2007 09:49:27 -0700
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.
On Aug 20, 9:04 pm, 4mula_freak
<4mula_fr...@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 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
.
- Prev by Date: Re: Counting Text Strings
- Next by Date: Re: Counting Text Strings
- Previous by thread: RE: EXCEL: How to scan text reversed (like ACCESS: InStrRev)?
- Next by thread: Re: EXCEL: How to scan text reversed (like ACCESS: InStrRev)?
- Index(es):
Relevant Pages
|
