Extracting using seperators

[starguy]

how can i extract the right most characters of a text after a seperator
suppose i have "asdf-12df-654" in cell B5 and "df-5d7-df7854" in Cell B6 and so on downwards. i want to extract the right most characters which are 654 in cell B5 and df7854 in B6. how can i do this by using function (not VBE)

thanks

[Dave Peterson]

=RIGHT(A1,LEN(A1)-FIND("^^",
SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

(all one cell)

And it assumes that ^^ doesn't appear in your string.

Or

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),
SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

(char(1) is not used very often in strings)

starguy wrote:
>
> how can i extract the right most characters of a text after a seperator
> suppose i have "asdf-12df-654" in cell B5 and "df-5d7-df7854" in Cell
> B6 and so on downwards. i want to extract the right most characters
> which are 654 in cell B5 and df7854 in B6. how can i do this by using
> function (not VBE)
>
> thanks
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=526267

--

Dave Peterson

[CLR]

Assumning your format is always the same with the two hyphens,......

=MID(A1,FIND("-",A1,FIND("-",A1,1)+1)+1,99)

Vaya con Dios,
Chuck, CABGx3


"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> how can i extract the right most characters of a text after a seperator
> suppose i have "asdf-12df-654" in cell B5 and "df-5d7-df7854" in Cell
> B6 and so on downwards. i want to extract the right most characters
> which are 654 in cell B5 and df7854 in B6. how can i do this by using
> function (not VBE)
>
> thanks
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=526267
>

[starguy]

thank you both.
formulas by Dave are more helpful for as number of separators in my data change and i want string at right side after last separator.

thank you both for replying.