This'll be one for the wizards

[Guest]

Hi,

I've got a string of names fillowed by unique IDs, e.g.

Arthur Dent (ArD01)
Ford Prefect (BA) London (FP02)
Harry Potter (HP3)
Inspector Morse (Det Supt) Oxford (Morsie12)

I need to return the unique IDs, e.g:
ArD01
FP02
HP3
Morsie12

I need to do this by formula as it's to feed a VLOOKUP.

I'd normally use a FIND on the ( and the ), but the cases with two sets of
brackets preclude this. There may even be several set of brackets. I need
the rightmost. The string and the unique ID can be ay length. Any ideas?

Cheers,

Tom.

 

[Peo Sjoblom]

One way

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


with the first entry in A1, copy down as long as needed

 

[Guest]

Give this a try:

=MID(SUBSTITUTE(A1,")","",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))),FIND("~",SUBSTITUTE(A1,"(","~",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,255)

HTH,
Elkar

 

[Guest]

With
A1 containing the base text

Try this:

B1:
=SUBSTITUTE(RIGHT(A1,MATCH("(",INDEX(MID(A1,LEN(A1)+1-ROW($A$1:INDEX($A:$A,LEN(A1),1)),1),0),0)-1),")","")

Copy B1 down as far as you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+2,LEN(MID(A1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+2,99))-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks, all.

I've left work for the weekend, but will try all of these on Monday.

I really am grateful for your help.

Tom.

 

[Guest]

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

 

[Guest]

Thanks, guys - they all worked.

Tom.

Thanks, all.

I've left work for the weekend, but will try all of these on Monday.

I really am grateful for your help.

Tom.