Separating Data into Rows

[rookie2525]

Hello,

I'm having issues separating cells into additional rows. I have fixed numbers in each cell. No success using text to columns - something keeps going wrong.

206463044
503525890
949439581

Ideally, I'd like them to look like:

1. 2066463044
2. 5035255890
3. 9494399581

Thanks! I'm horrible at Excel, but trying.

[AlKey]

Use this formula in B1 and copy down

=TRIM(MID(SUBSTITUTE(CHAR(10)&$A$1,CHAR(10),REPT(" ",50)),50*ROWS($A$1:A1),50))

Row\Col	A	B
1	206463044 503525890 949439581	206463044
2		503525890
3		949439581

[newdoverman]

You could use a formula like this. I am starting the data in A5 and the formula is in B5

Please Login or Register  to view this content.

[newdoverman]

Oops, misread the problem....again

[newdoverman]

Took another look and came up with this:

Please Login or Register  to view this content.

If data in say B14, the formula would be:

Please Login or Register  to view this content.

AlKey's formula will do the same thing. That is you don't have to keep track of what row and column you are starting in as cell references are determining the start row.

	A	B
1	206463044 503525890 949439581	206463044
2		503525890
3		949439581

[rookie2525]

Thank you AlKey! Does this formula work if I have up to 500 cells? Also, all are 10 digit numbers but some cells have 2 phone numbers, and some have up to 5, but they all need to be in an individual row.

[AlKey]

Thank you AlKey! Does this formula work if I have up to 500 cells? Also, all are 10 digit numbers but some cells have 2 phone numbers, and some have up to 5, but they all need to be in an individual row.

This is something that would work much better if extracted to the columns instead of down to the rows.

like this:
pull formula to the right until you see blanks and then down


=TRIM(MID(SUBSTITUTE(CHAR(10)&$A1,CHAR(10),REPT(" ",50)),50*COLUMNS($A:A),50))

Row\Col	A	B	C	D
1	206463044 503525890 949439581	206463044	503525890	949439581
2	654987123 459878652 3219876548	654987123	459878652	3219876548

[rookie2525]

Thank you both, been a great help!!

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation *

[newdoverman]

If you want to convert AlKey's horizontal extraction to a column
Enter in column H the following

Please Login or Register  to view this content.

Enter in column I to remove all blank cells:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C	D	E	F	G	H	I
1	206463044 503525890 949439581	206463044	503525890	949439581				206463044	206463044
2	654987123 459878652 321987654 206463044 503525890	654987123	459878652	321987654	206463044	503525890		503525890	503525890
3	654987123 459878652 321987654	654987123	459878652	321987654				949439581	949439581
4									654987123
5									459878652
6									321987654
7								654987123	206463044
8								459878652	503525890
9								321987654	654987123
10								206463044	459878652
11								503525890	321987654
12
13								654987123
14								459878652
15								321987654