This'll be one for the wizards

M

mr tom

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

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
 
E

Elkar

Give this a try:

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

HTH,
Elkar
 
R

Ron Coderre

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
 
B

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

mr 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.
 
T

Teethless mama

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

mr tom

Thanks, guys - they all worked.

Tom.

mr tom said:
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.
Click to expand...
 
Top