Extracting a segments from each cell

[Excel_77]

Hi All,

In column D I have a person's name along with their unique ID so for example the format would be in "Bloggs, Joe (12345678)", what I want is in column E to include the unique ID for each person without the brackets (), is there a quick and easy way to do this?

Thanks

 

[Special-K99]

If ALL the IDs are 8 digits long and there's no other data on the right of the ID (apart form the right bracket) then this should work

=SUBSTITUTE(RIGHT(A1,9),")","")

Otherwise this will find the first left bracket and extract up till the next right bracket regardless of the length of the ID.

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

 

[Excel_77]

If ALL the IDs are 8 digits long and there's no other data on the right of the ID (apart form the right bracket) then this should work

=SUBSTITUTE(RIGHT(A1,9),")","")

Otherwise this will find the first left bracket and extract up till the next right bracket regardless of the length of the ID.

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

Superb, thanks so much. I have opted for the below code that you provided, however could you explain what it means? I would struggle to build this code on my own.

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

Thanks again.

 

[Special-K99]

It finds the first left bracket in A1 - FIND("(",A1)
It finds the first right bracket in A1 - FIND(")",A1)
and extracts the data between the brackets - MID(A1,left bracket position+1,right bracket position-1)

 

[Excel_77]

It finds the first left bracket in A1 - FIND("(",A1)
It finds the first right bracket in A1 - FIND(")",A1)
and extracts the data between the brackets - MID(A1,left bracket position+1,right bracket position-1)

I think I almost get it but I want to double check this with you:

=MID(A1, This returns the middle value between the brackets

FIND("(",A1)+1, - Find the first left bracket "(" and that use the first character after this (+1)

FIND(")",A1)- Not sure what this does in the middle

FIND("(",A1)-1) Find the first right bracket ")" and that use the first character before this (-1)

 

[Special-K99]

That's right, yes.

Google for descriptions and syntax of the formulas

 

[Excel_77]

That's right, yes.

Google for descriptions and syntax of the formulas

I think I've got it, I'm still not quite sure with the middle part and what it does - FIND(")",