Split cell contents by comma with formula *Can't use text to columns*

**DannyJ** · 09-20-2017, 05:06 AM

Hi all,

I have a cell that has numerous values in it. I would like a formula that I can drag all the way down a column that will take each value and put it one below the other.

Text to columns can't be used because I would like it to be dynamic, whenever a number is added to the cell is is automatically put into the column too.

This is to be done on a google docs or numbers for apple so I also can't use vba.

Unfortunately I can't attached a file but to try and explain:

A1 would contain:

2.10,3,4.65,6,1.2

The formula which will be in a seperate sheet will read

A1: 2.10
A2: 3
A3: 4.65
A4: 6
A5: 1.2

Thanks in advance,

Danny

**shukla.ankur281190** · 09-20-2017, 05:23 AM

Row\Col	A
1	2.10,3,4.65,6,1.2
2	2.1
3	3
4	4.65
5	6
6	1.2
7

A2

Formula:

Please Login or Register  to view this content.

Drag down!!

**benishiryo** · 09-20-2017, 05:24 AM

hi danny. assuming data in Sheet1, try:
=IFERROR(--TRIM(MID(SUBSTITUTE(Sheet1!$A$1,",",REPT(" ",LEN(Sheet1!$A$1))),1+(ROWS($A$1:A1)-1)*LEN(Sheet1!$A$1),LEN(Sheet1!$A$1))),"")

**DannyJ** · 09-20-2017, 06:36 AM

These work perfectly. Thank you very much.

**shukla.ankur281190** · 09-20-2017, 06:47 AM

Thanks for feedback
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Split cell contents by comma with formula Can't use text to columns

LinkBack

Thread Tools

Rate This Thread

Display