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

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.

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

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C:D)-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


On Thu, 7 Aug 2008 14:57:00 -0700, Mike
wrote:

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

Thank you

"Gord Dibben" wrote:

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C:D)-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


On Thu, 7 Aug 2008 14:57:00 -0700, Mike
wrote:

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

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?

"Gord Dibben" wrote:

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C:D)-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


On Thu, 7 Aug 2008 14:57:00 -0700, Mike
wrote:

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

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" wrote:

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C:D)-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


On Thu, 7 Aug 2008 14:57:00 -0700, Mike
wrote:

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.

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

You can copypaste special in place should you choose.

But you found a workaround.


Gord

On Fri, 8 Aug 2008 05:27:00 -0700, Mike
wrote:


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" wrote:

Enter this formula in D1 and copy across to F1

=INDEX($C:$C,(ROWS($1:1)-1)*3+COLUMNS($C:D)-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


On Thu, 7 Aug 2008 14:57:00 -0700, Mike
wrote:

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.

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Mike" wrote:

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.