Split two-item info from one cell to two cells

JamieA

I calculate the commission from the profit.

Example:

These are in the same cell:

AllQlear 60 tabs ---> $10.53
Alpha Lipoic Acid ---> $13.46

I want "AllQlear 60 tabs" to be in the cell next to it and "$10.53" in the cell after that.

I am very new to smartsheets. I'd appreciate if there is a formula you could suggest that I could just copy and paste. I've been searching for a while and just get confused by the answers. Thank you in advance!

SoS | Dan Palenchar

Hey @JamieA!

If the data is exactly in this format meaning there is (i) the name of the item and a space, (ii) ---> and a space, and (iii) a price, you could separate it with a combination of LEN() LEFT() RIGHT() and FIND().

An easy example - if the price is always 6 between $10.00 and $99.99 then you can pull out the five rightmost characters using:

=RIGHT([Column With Data]@row, 5)

This will return, in your first example 10.53. To get it to function as a number you would need to nest RIGHT() within VALUE() as

=VALUE(RIGHT([Column With Data]@row, 5))

Then you could format this cell as currency.

Getting the name of the item is a little tricky as it is variable, but if you always. have that arrow starting with a hyphen in the middle and never have another instance of a hyphen you could use FIND() to get the position where that occurs and subtract it from LEN() which gives the length of the string. You would then nest this in VALUE(LEFT()).

Hope that helps!