Help with an extract string formula

[SEMMatt]

Hello,

I have data in this format,

(aberdeen city, united kingdom)

. What formula can I use to extract the data into two cells (A2 and A3 for instance)? It would look like this: A2: aberdeen city, A#: united kingdom. Sometimes they'll be data before (,) but it will always be in this format: (,)

Thanks,

[shg]

Data > Text to columns ...

[Moo the Dog]

Try these:

A2: =SUBSTITUTE(LEFT(A1,FIND(",",A1)-1),"(","")
A3: =SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(",",A1)-1),")","")

- Moo

[SEMMatt]

Moo,

Thanks that's closer but that gave me New York, New York (in A2) and United States (in A3). In this case, of

New York (New York, United States)

how would I separate all three into different columns?

Thanks,

Matt

[AlKey]

Use this formula in B1 and pull it to the right and then down

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&SUBSTITUTE($A1,"(",""),")",""),",",REPT(" ",125)),125*COLUMNS($A:A),125))

Row\Col	A	B	C
1	(aberdeen city, united kingdom)	aberdeen city	united kingdom

[SEMMatt]

AlKey,

Thank you but A1 will always be like this:

New York (New York, United States)

?

[AlKey]

Sorry, I overlooked that

=TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(REPLACE($A1,1,FIND("(",$A1),""),")",""),",",REPT(" ",125)),125*COLUMNS($A:A),125))

Row\Col	A	B	C
1	New York (New York, United States)	New York	United States

[SEMMatt]

AlKey, Perfect! Thanks

[SEMMatt]

AlKey, sorry to say but it wasn't quite perfect. I need it to be New York in column B and New York in column C. United States isn't important for this. Can you help please?

[bebo021999]

How about in B1:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",REPT(" ",100)),",",REPT(" ",100)),")",""),100))

C1:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",REPT(" ",100)),",",REPT(" ",100)),")",""),100,100))

D1:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",REPT(" ",100)),",",REPT(" ",100)),")",""),100))

[SEMMatt]

bingo! thank you bebo

[AlKey]

Like this?

in B1 and pull formula to the right

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&SUBSTITUTE($A1," (",","),")",""),",",REPT(" ",125)),125*COLUMNS($A:A),125))

Row\Col	A	B	C	D
1	New York (New York, United States)	New York	New York	United States