Extracting all text from parentheses (including parentheses)

**j4ke101** · 05-10-2013, 05:42 AM

Hi guys,

I have a large number of rows of cells with text that includes part numbers in parentheses. For example:

Fitted: 2xPST TYPE 3 2770(RW44-00090A),1xFUSER - 2770(RW96-03800C)

I need to extract the part numbers from these parentheses and separate them by a comma so it would look like this:

(RW44-00090A), (RW96-03800C)

Any ideas how i can acheive this with a formula?

Thanks in advance!

**martindwilson** · 05-10-2013, 06:06 AM

how many part numbers is it always 2?

**j4ke101** · 05-10-2013, 06:11 AM

No, it can be anything from 1-5 part numbers...

**martindwilson** · 05-10-2013, 06:13 AM

that1 awkward to do in onw cell with one formula! youd be better of using 5 seperate cells then joining them or maybe a vba solution

**j4ke101** · 05-10-2013, 06:16 AM

Do you know a formula that would work if i used it 5 times? Presumably after that i could just use =concatenate to join them up?

**martindwilson** · 05-10-2013, 06:41 AM

yep here you go

**Kyle123** · 05-10-2013, 06:45 AM

Untested, but I suspect VBA would be something like this:

Actually can that, was thinking a bit too simply

**Ghozi Alkatiri** · 05-10-2013, 07:14 AM

if only 2 number to split try this
=LEFT(MID(A1,FIND("(",A1),LEN(A1)),FIND(",",MID(A1,FIND("(",A1),LEN(A1))))&MID(MID(A1,FIND("(",A1),LEN(A1)),FIND("(",MID(A1,FIND("(",A1),LEN(A1)),2),LEN(A1))

**martindwilson** · 05-10-2013, 07:24 AM

it think you have not read the thread ghozi

No, it can be anything from 1-5 part numbers.

**j4ke101** · 05-10-2013, 07:27 AM

Thanks martindwilson - that worked perfectly. It might not be the prettiest solution but it has saved me a huge amount of work

**Pepe Le Mokko** · 05-10-2013, 07:34 AM

If all strings have the same structure Data-Text to columns also does the job

**martindwilson** · 05-10-2013, 07:36 AM

yea thats a point,then join them up after

**Marcol** · 05-10-2013, 08:03 AM

Slightly shorter than Martins', seems to work ok.

This is setup for up to 6 number groups, if you need more drag C2 across as required, and extend the concatination in B2 to suit.