Separate cell data ( based on color)?

A_K

New Member
Joined
Mar 12, 2013
Messages
8
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

Product139(1%)
Product2398(10.16%)
Product3432(11.02%)
Product42756(70.3%)
Product5456(11.64%)
Product6457(11.66%)
Product7482(12.3%)
Product8328(8.37%)
Product92884(73.6%)

<colgroup><col><col></colgroup><tbody>
</tbody>
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Mar 12, 2013
Messages
8
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!
 

Forum statistics

Threads
1,085,565
Messages
5,384,469
Members
401,903
Latest member
MarBer

Some videos you may like

This Week's Hot Topics

Top