Re: Extracting Text from the right

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("^^",SUBSTITUTE(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 <F4D923B7-8A2C-471D-9B46-C3777004DEFC@xxxxxxxxxxxxx>,
"L Ellis" <LEllis@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: extract chars after a last dot
    ... > how do i extract the characters after the last dot? ... If you use bash, you can do this: ...
    (comp.unix.shell)
  • Re: Extracting a pattern of numbers from a text string
    ... My goal is to extract just the numbers out of this text string ... If job numbers ALWAYS look like that (ie. two digits, dot, four digits) ... These can be calculations in a Set Field script command or Calculation ...
    (comp.databases.filemaker)
  • Re: File Find & Extract
    ... jiggery-pokery in an "Obey" file will do the trick. ... The relevant syntax is: ... extract all the jpegs from a directory which has a huge ... j dot mccartney atte blueyonder dot co dot uk ...
    (comp.sys.acorn.apps)
  • Re: extract chars after a last dot
    ... On Mon, 04 Aug 2003 at 11:22 GMT, mike wrote: ... > how do i extract the characters after the last dot? ...
    (comp.unix.shell)
  • DBXtract
    ... is "Action" and I have a dot in "Save" and a check ... the address of one of the dbx folders. ... I then clicked on "Extract", ... Then when I go to My Documents/DBX I find "DBXtract.exe, ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)