Excel: data manipulation

I have a spreadsheet, and the cells in the sheet have their data in the form of (x(n),y(n))… those are subscripts.

For example, the first 3 cells in the 1st column have data that looks like this: (1, 2),(123, 1231), and (12, 1231). This is great, since the pair of numbers in each cell can be thought of as coordinates in some cases. However, for a lot of my work I would like to break this data out so that each x(n) and y(n) have their own cell. This process should double the number of columns in my table.

Original:

Column A
(1, 2)
(123, 1231)
(12, 1231)

Intended:

Column B     Column C
1            2
1开发者_运维问答23          1231
12           1231

The original set gets split into two columns; the first having 1, 123, and 12 populating the cells, while the next column aligns with 2, 1231, 1231. Does anyone know how I can do this in Excel?

If your input is well formed (there are no extra spaces around the parens, there is always exactly one comma and it is always followed by exactly one space) then you could use the following to get the x coordinate from A1:

=INT(MID(A1,2,FIND(",",A1)-2))

And the following for the y coordinate (assuming the above is in B1):

=INT(MID(A1,FIND(",",A1)+2,LEN(A1)-LEN(B1)-4))

This function extract the number on the left of the comma

=LEFT(A1,FIND(",",A1)-1)

and this one the part on the right

=RIGHT(A1,LEN(A1)-FIND(",",A1))

EDIT. Sorry, if you have also parentheses my functions become

=LEFT(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),FIND(",",A1)-2)

for the left part and

=TRIM(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(",",A1)),")",""))

for the right one.