Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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))) |
#7
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting text data in a cell | Excel Worksheet Functions | |||
Extracting a 'number' from text | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Extracting and using Text from external sources | Excel Worksheet Functions | |||
EXTRACTING TEXT | Excel Discussion (Misc queries) |