Separate cell data ( based on color)?

[A_K]

Hi, I have downloaded the data in the table, and it came with the percent numbers (in grey) stuck to the volume numbers (in black). I have 400 rows like this and need two columns - one for volume numbers and the other for percent numbers. Because I have various numbers of characters in each row that need to be separated, I cannot use a LEFT/RIGHT formula. Is there an alternative way to separate these? two

Many thanks!
A_K

[TABLE="width: 193"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product1[/TD]
[TD]39(1%)[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]398(10.16%)[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]432(11.02%)[/TD]
[/TR]
[TR]
[TD]Product4[/TD]
[TD]2756(70.3%)[/TD]
[/TR]
[TR]
[TD]Product5[/TD]
[TD]456(11.64%)[/TD]
[/TR]
[TR]
[TD]Product6[/TD]
[TD]457(11.66%)[/TD]
[/TR]
[TR]
[TD]Product7[/TD]
[TD]482(12.3%)[/TD]
[/TR]
[TR]
[TD]Product8[/TD]
[TD]328(8.37%)[/TD]
[/TR]
[TR]
[TD]Product9[/TD]
[TD]2884(73.6%)[/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

The easiest way would be to use Text to Columns, with the bracket "(" as the delimiter. Or if you'd prefer a formula approach, try this:
C2:=LEFT(B2,FIND("(",B2)-1)
D2: =SUBSTITUTE(B2,C2,"")

 

[Andrew Poulsom]

Can't you use these formulas in C1 and D1 copied down?

=LEFT(B1,FIND("(",B1)-1)+0
=REPLACE(B1,1,LEN(C1),"")+0

 

[A_K]

Thank you, njimack, Andrew,

I have tried with the formulas - they work, but return negative values, because of the parenthesis. So eventually I used Text to Columns proposed by njimack and then replaced the remaining ")" with nothing.

Thank you for help!