Break the number(s) to different cells

[I.am.Rustam]

Hi guys,

Please help me with this:


Cell A1: $16.75/2, $17.25/1, $17.50/2

Cell A2: $16.00/1; $20.00/1; $22.00/1

Cell A3: $11.18/1 $11.18/1 $11.51/1 $11.86/1

Cell A4: $20.00/1, $24.00/1, $22.00/1, $33.00/term

Cell A5: $25.50/1, $26.27/1, $27.05/1 month

etc...

I need to have in cell B1: 16.75, C1: 2, D1: 17.25, E1: 1 ,etc.... However, if there is any text in the cell (like in cell A4 and A5) I would need to see "False" or "error" even in the B column

Text-to-Column doesn't work since numbers above derived by formula from another column.

Also, please note that numbers are separated by either comma, semi-comma, or nothing at all. (eg. a1, a2, a3)


Please help! Thanks a lot!

[AliGW]

Do you need to retain the formulae in column A?

[I.am.Rustam]

Ali,

No. I don't need to retain formulas. Can copy to column B if needed.

[AliGW]

OK, so what I would do is copy the data as values to the same or another column, then use Text to Columns to split first on the space, then on the /. After that, you can clean up the cells using Find and Replace.

[AlKey]

Try this formula in B1 and copy across and then down

Please Login or Register  to view this content.

Data Range

	A	B	C	D	E	F	G	H	I	J
1	$16.75/2, $17.25/1, $17.50/2	16.75	2	17.25	1	17.5	2	FALSE	FALSE	FALSE
2	$16.00/1; $20.00/1; $22.00/1	16	1	20	1	22	1	FALSE	FALSE	FALSE
3	$11.18/1 $11.18/1 $11.51/1 $11.86/1	11.18	1	11.18	1	11.51	1	11.86	1	FALSE
4	$20.00/1, $24.00/1, $22.00/1, $33.00/term	20	1	24	1	22	1	33	FALSE	FALSE
5	$25.50/1, $26.27/1, $27.05/1 month	25.5	1	26.27	1	27.05	1	FALSE	FALSE	FALSE

[I.am.Rustam]

AliGW,

Text to column isn't working for me in this case, since I have other steps to do with formatted cells. The goal is to create macro eventually....

Thanks though!

[I.am.Rustam]

Alkey,

Thanks a lot. Your formula works fine!