Numbers – problems with a formula for “text to columns” behavior

numbers

I have a column with three comma separated RGB values that I want separated into three columns by formula, meaning I want a sheet that lets me paste "0, 0, 0" or "0,0,0", or "255, 255, 255" etc. and the next three columns will contain the isolated values.

I'm not looking for a solution that changes the values one time only like renaming the file to .csv and letting it happen automatically the first time the file is opened.

Basically the number of characters will vary between 1 and 3 for each field, and there may or may not be a space after the comma before the next value.

enter image description here

As a starting point, I tried C2=LEFT($B2,FIND(",",$B2)) but I don't see how to have the result Not include the comma.

I was thinking that I could do something like this (pseudocode):

C2 = Left of comma in B2
D2 = Left of comma in (Right of comma in B2)
E2 = Right of comma in (Right of comma in B2)

Is this possible?

I was hoping to do something similar to the way I separated hex color values but that is simpler because it's always six characters.

enter image description here

Here, the formulas are:

hexR = REPLACE($hexRGB,3,4, )
hexG = REPLACE(REPLACE($hexRGB,1,2,),3,2,)
hexB = REPLACE($hexRGB,1,4,)

Best Answer

Can you try these pls (assuming the text to be separated is in B2)?

C2 = LEFT(B2,FIND(",",B2)−1)
D2 = MID(B2,FIND(",",B2)+1,FIND(",",B2,FIND(",",B2,)+1)−FIND(",",B2)−1)
E2 = RIGHT(B2,LEN(B2)−FIND(",",B2,FIND(",",B2,)+1))

They should work for all values of non-zero length between the commas assuming there are always exactly 2 commas.

Related Question