Extracting using seperators

S

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
 
F

flummi

One way would be to use this if there are only and always 2 dashes:
col A______________col B
asdf-12df-654 654
df-5d7-df7854 df7854

Formula in col B: =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

Regards

Hans
 
J

Jim May

Copy this code into a Standard Module:

Function findr(ByVal s As String, ByVal src As String) As Long
findr = Len(src) - Application.Find(StrReverse(s), StrReverse(src))
End Function

Then with your samples in A1:A2
asdf-12df-654
df-5d7-df7854

In B1 enter
=RIGHT(A1,LEN(A1)-findr("-",A1)-1)



and copy down
HTH,
Jim May
 
D

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

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
 
S

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