stripping out a text string

[Guest]

I have a column of cells with text strings of varying lengths. The last few
characters of each string is an ID that I need to separate out from the rest
of the string. The ID follow the formate 3-characters, dash,
more-characters. Below are some examples of the text strings. I need help
finding a column that will strip out just the ID from each string. I tried
=RIGHT(D98,(LEN(D98)-FIND("-",D98)+4)) but that failed because at least one
string has a dash to the left of the ID. Perhaps there is a way to use the
FIND function, but starting from the right rather than the left? If a
function won’t work, I am open to a VBA solution. Thanks.

Examples:
USD/CDS/MEX/BBVA-Madrid/E02-93622
USD/CDS/COLOMBIA/E04-90881
USD/CDS/MEXICO/E02-91435-A
USD/CDS/MEXICO/M03-90870

 

[RagDyer]

Try this:

=MID(D98,SEARCH("/???-",D98)+1,LEN(D98)-SEARCH("/???-",D98)+1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a column of cells with text strings of varying lengths. The last few
characters of each string is an ID that I need to separate out from the rest
of the string. The ID follow the formate 3-characters, dash,
more-characters. Below are some examples of the text strings. I need help
finding a column that will strip out just the ID from each string. I tried
=RIGHT(D98,(LEN(D98)-FIND("-",D98)+4)) but that failed because at least one
string has a dash to the left of the ID. Perhaps there is a way to use the
FIND function, but starting from the right rather than the left? If a
function won’t work, I am open to a VBA solution. Thanks.

Examples:
USD/CDS/MEX/BBVA-Madrid/E02-93622
USD/CDS/COLOMBIA/E04-90881
USD/CDS/MEXICO/E02-91435-A
USD/CDS/MEXICO/M03-90870

 

[JasonMorin]

=MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN
(SUBSTITUTE(A1,"/",""))))+1,999)

HTH
Jason
Atlanta, GA

 

[Guest]

I'm impressed. Thanks.

=MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN
(SUBSTITUTE(A1,"/",""))))+1,999)

HTH
Jason
Atlanta, GA

 

[hgrove]

benb wrote...

I have a column of cells with text strings of varying lengths. Th last few
characters of each string is an ID that I need to separate out fro the rest of the
string. The ID follow the formate 3-characters, dash more-characters. Below are
some examples of the text strings. . . . ...
Examples:
USD/CDS/MEX/BBVA-Madrid/E02-93622
USD/CDS/COLOMBIA/E04-90881
USD/CDS/MEXICO/E02-91435-A
USD/CDS/MEXICO/M03-90870

An alternative approach. If these strings could contain arbitrar
characters, then the safest way to do this involves using a defien
name like Seq referring to

=ROW(INDIRECT("1:1024"))

which returns an array of consecutive integers from 1 to 1024. Then us
it in formulas like

=RIGHT(A1,LEN(A1)-LOOKUP(2,1/(MID(A1,Seq,1)="/"),Seq))

or

=MID(A1,LOOKUP(2,1/(MID(A1,seq,1)="/"),seq)+1,1024