Extracting Text from the right
 

Hi -

Am looking for some help writing an equation to extract from the right of a
string.

Here is a sample of data:

esolent.com
1glend.dialup10.famvid.com
1n7.8774.1n-129.nethost4.biz
1n7-93.servernode.net
2.inexo.com
2.wencor.com.uk
20.tieless.com
9-inet3.static.ac1-lax.aubeta.net
bsdwebsolutions.com
cache.es

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

The standard excel formulas count from the left to the right and not the
right to the left, thus my problem.

Thanks!
L

you need ot first findout how many "." there are
=len(A1)-len(substitute(A1,".",""))
you then need to find where the last one is
=find(".",A!,len(A1)-len(substitute(A1,".","")))

to extract what is to the right of the last .
=right(A1,len(A1)-find(".",A1,len(A1)-len(substitute(A1,".",""))))

and to the left
=left(A1,find(".",A1,len(A1)-len(substitute(A1,".","")))-1)

"L Ellis" wrote:

Hi -

Am looking for some help writing an equation to extract from the right of a
string.

Here is a sample of data:

esolent.com
1glend.dialup10.famvid.com
1n7.8774.1n-129.nethost4.biz
1n7-93.servernode.net
2.inexo.com
2.wencor.com.uk
20.tieless.com
9-inet3.static.ac1-lax.aubeta.net
bsdwebsolutions.com
cache.es

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

The standard excel formulas count from the left to the right and not the
right to the left, thus my problem.

Thanks!
L

Opps, I goofed up, More later

"L Ellis" wrote:

Hi -

Am looking for some help writing an equation to extract from the right of a
string.

Here is a sample of data:

esolent.com
1glend.dialup10.famvid.com
1n7.8774.1n-129.nethost4.biz
1n7-93.servernode.net
2.inexo.com
2.wencor.com.uk
20.tieless.com
9-inet3.static.ac1-lax.aubeta.net
bsdwebsolutions.com
cache.es

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

The standard excel formulas count from the left to the right and not the
right to the left, thus my problem.

Thanks!
L

Since there can be more than one "." in the text string, try this (for text
in Cell A1):

For the chars to the right of the last ".":
=RIGHT(A1,MATCH(".",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)-1)

For the chars to the left of the last ".":
=LEFT(A1,LEN(A1)-MATCH(".",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)-1)

NOTE: Commit both of those array formulas by pressing [Ctrl]+[Shift]+[Enter]

(The formulas reverse the text and search for the "." from the left.)

Does that help?
--
Regards,
Ron


"L Ellis" wrote:

Hi -

Am looking for some help writing an equation to extract from the right of a
string.

Here is a sample of data:

esolent.com
1glend.dialup10.famvid.com
1n7.8774.1n-129.nethost4.biz
1n7-93.servernode.net
2.inexo.com
2.wencor.com.uk
20.tieless.com
9-inet3.static.ac1-lax.aubeta.net
bsdwebsolutions.com
cache.es

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

The standard excel formulas count from the left to the right and not the
right to the left, thus my problem.

Thanks!
L

Try the following...

To extract left of the last dot:

B1, copied down:

=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,".","^^",LEN(A1 )-LEN(SUBSTITUTE(A1,"."
,""))))-1)

To extract right of the last dot:

C1, copied down:

=RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITU
TE(A1,".","")))))

OR

=RIGHT(A1,LEN(A1)-(LEN(B1)+1))

If some values do not contain a dot extension, try the following...

=IF(ISNUMBER(SEARCH(".",A1)),LEFT(A1,SEARCH("^^",S UBSTITUTE(A1,".","^^",L
EN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1),A1)

and

=IF(ISNUMBER(SEARCH(".",A1)),RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1,"
..","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))),A1)

Hope this helps!

In article ,
"L Ellis" wrote:

Hi -

Am looking for some help writing an equation to extract from the right of a
string.

Here is a sample of data:

esolent.com
1glend.dialup10.famvid.com
1n7.8774.1n-129.nethost4.biz
1n7-93.servernode.net
2.inexo.com
2.wencor.com.uk
20.tieless.com
9-inet3.static.ac1-lax.aubeta.net
bsdwebsolutions.com
cache.es

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

The standard excel formulas count from the left to the right and not the
right to the left, thus my problem.

Thanks!
L

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/...74d1d78a685f59

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

Nice improvement to my formula, Harlan....Thanks.

----
Regards,
Ron


"Harlan Grove" wrote:

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)