how do I unmerge data in a column and distribute in rows?

[Mike]

I have data from an address base that when cut and pasted gourped or merged
three cells. I would like to unmerge them and distribute the information from
3 cells in a column to three cells in a row. I have 1400 addresses, so doing
this manually is not logical. The "unmerge" command does not do anything.

 

[M Kan]

If they're delimited in a consistent fashion, you can use the Text to Column
function to parse them out. Go to Data: Text to Column and you have the
options of parsing by delimiter or by fixed width.

 

[Mike]

They aren't I have a street address in C1, a city state and zip and c2 and a
phone number in c3. The sequence then repeats itself. I obviously can copy
the three cells and paste special and then transpose them and get them lined
up. Then I neeed to delete the originals and cut and paste to get in proper
sequence. What I am looking for is a more efficent way of doing the steps
noted above.

 

[Gord Dibben]

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C)-1)

Select D1:F1 and copy down untill you get zeros showing up.

When happy, paste special as values then delete column C


Gord Dibben MS Excel MVP

 

[Mike]

Thank you

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C)-1)

Select D1:F1 and copy down untill you get zeros showing up.

When happy, paste special as values then delete column C


Gord Dibben MS Excel MVP

 

[Mike]

THe formula fills perfectly but by itself. When I delete the source column
however I end up with #REF error. How do I do the paste special function
since the formula acts on its own without a copy action?

 

[Mike]

Never mind I figured it out. I copied the section to another sheet and
pasted special. Thnak you thisa has saved me hours of work.

 

[Gord Dibben]

You can copy>paste special in place should you choose.

But you found a workaround.


Gord