Convert numbers in cell to individual rows in excel?

C

cemun

I have a spreadsheet that has many cells with up to 50 different account
numbers in the cell that need to be converted to individual rows within the
sheet. How can I accomplish this task?
Thanks for any help you can provide.
 
G

Gary''s Student

There good built-in functions to help you.

1. use Text to Columns to move the account numbers from a single cell to one
cell each in the same row.

2. use the TRANSPOSE() function to data in the rows to columns.
 
C

cemun

That would be great for columns, but I need the information to be converted
to rows. The cell might look like this:
123452 152233 45232 255422
I need a row for each number.
 
G

Gary''s Student

Then all you need is Text to columns. Just for example, if A1 contained your
posted data:

123452 152233 45232 255422
then Text to Columns could put
123452 in B1
152233 in C1
45232 in D1
255422 in E1

At this point, individual cells (all in the same row)

and =TRANSPOSE(B1:E1) as an array formula will put:

123452 in A2
152233 in A3
45232 in A4
255422 in A5

At this point, individual cells, each in its own row
 
C

cemun

Instead of columns, I need rows.
cell: 123452 152233 45232 255422
converted data to
Row:
1) 123452
2) 152233
3) 45232
4) 255422
 
G

Gord Dibben

Re-read the posting.

Two steps........break the numbers across 50 columns then transpose to 50 rows
in one column.


Gord Dibben MS Excel MVP
 
Top