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,086,234
Messages
5,388,609
Members
402,126
Latest member
kalcerro_1

Some videos you may like

This Week's Hot Topics

Top