Re: Extracting Text from the right

L Ellis wrote...
>Am looking for some help writing an equation to extract from the right of a
>string.
....
>What I want to do is write two equations:
>
>1) To extract to the right of the last "."
>2) To extract to the left of the last "."
....

Others have already responded with the standard approaches. I'd modify
the approach Ron Coderre took. Define a name like seq referring to the
formula

=ROW(INDIRECT("1:1024"))

Then use regular (nonarray) formulas like

1. =MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="."),seq)+1,1024)

2. =LEFT(A1,LOOKUP(2,1/(MID(A1,seq,1)="."),seq)-1)


Another approach involves VBA. VBA can make use VBScript regular
expressions. Using the Subst function from

http://groups-beta.google.com/group/microsoft.public.excel.work***.functions/msg/9d74d1d78a685f59

(or http://makeashorterlink.com/?J17D21B3B ), you could use formulas
like

1. =subst(A1,".+\.","")

2. =subst(A1,"\.[^.]*$","")

[Excel isn't a good text processing tool. Regular expressions are the
most powerful text parsing tools developed to date. If you do this a
lot or your parsing tasks become more complicated, regular expressions
earn their keep.]


Finally, if all your parsing are domain names, splitting off top level
domain qualifiers, I believe the longest ones are 4 chars and the
shortest 2 chars. Unless there are domain names with a single char
immediately before the top level qualifier, e.g., www.x.us (which I
believe aren't allowed), all you need to look at are the last 5
characters in the string.

1. =RIGHT(A1,5-FIND(".",RIGHT(A1,5)))

2. =LEFT(A1,LEN(A1)-6+FIND(".",RIGHT(A1,5)))

.