RE: Extracting Text from the right

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
.

Relevant Pages

  • Re: Extracting Text from the right
    ... >1) To extract to the right of the last "." ... lot or your parsing tasks become more complicated, regular expressions ... shortest 2 chars. ... characters in the string. ...
    (microsoft.public.excel.worksheet.functions)
  • extract text between multiple char delimiters rather than a single char
    ... Rather than using cut for example, is it possible and straight forward ... enough to extract text between a given set of chars? ... "I am a string with some words and symbols and it doesn't make much ... What if I want to extract anything between the word "some" as a start ...
    (comp.unix.shell)
  • Re: atoi query
    ... qwe123 and extract the numeric portion, ... Have I misunderstood the strtol function? ... It can't extract a number ... from chars, so it fails. ...
    (comp.lang.c)
  • Re: Need to print 2 chars (or ints) from a string, sugestions?
    ... D54C ... is extract the integer from this string. ... this case can be treated as chars, ...
    (comp.lang.awk)
  • selecting short words
    ... i need to extract from a list of words (in a single column) anotherl list containing only the words of 5 or less chars. ...
    (perl.beginners)