Extract Unique IDs

[dhamway]

Hi everyone - Is there a way to ONLY extract data from between 2 parenthesis if the data is a number? See attached workbook.

[AliGW]

In C2 copied down:

=IFERROR(--SUBSTITUTE(MID(A2,FIND("(",A2)+1,99),")",""),"")

[KOKOSEK]

Into C2:
(As I know from your other post ID are always 6 digits long)

Please Login or Register  to view this content.

and drag it down.

[AliGW]

I think you are over-thinking it again ...

[Fluff13]

Another option, if it's always a 6digit number
=IFERROR(SUBSTITUTE(RIGHT(A2,7),")","")+0,"")

[dhamway]

WOW, again both great answers and both worked! You guys are amazing. Thank you so much. I will mark the thread as resolved. Happy new year!

[AliGW]

Happy New Year!

[dhamway]

Hi everyone - I forgot to add a situation to the example (my apologies). This is another data point
John Smith (10127677)[C]

As you can see, there is a [C] at the end. I also need to ignore that and still extract the 8 digit ID. Can you help me modify the formula? The current one just skips over this record and gives me a blank .

[AliGW]

I’m away from the PC now, but will look in again in the morning to see if you still need help.

[Sam Capricci]

this is an adjustment to what Ali gave you AND it assumes that if it exists it will always be [C]...
=IFERROR(--SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("(",A2)+1,99),")",""),"[C]",""),"")

[AliGW]

Thanks for the assist, Sam.

[Sam Capricci]

You're welcome Ali, I was trying to come up with my own when you already posted and I was also over thinking it and yours looked so efficient. A little tweak wasn't much to add.

[KOKOSEK]

Just in case if ID change to 7 or 9 and something like [X] or [DF] appear:

Please Login or Register  to view this content.

[AliGW]

This is a bit shorter:

=IFERROR(--TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("(",A2)+1,99),")",""),"[",REPT(" ",99)),98)),"")

[dhamway]

Thank you everyone! Worked like a charm. I'll mark this as resolved.