extracting from 1 collumn mailing list

[JohnW]

Hello,
I have an extracted file where the mailing list is in one collumn.
eg. the name, address1, address2, address3 and blank line is repeated down
the page. Just like a lable printout.
Is there a way of selectively copying all names to 1 row, address1 to the
next row, address2 to the next and so on.
I think it is in a format of copying every 5th line to a collumn?
That way I'll have 4 collums of data.

 

[Gord Dibben]

Enter this formula in B1 and copy across to E1

=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Select B1:E1 and drag/copy down until you get zeros.

When happy, select columns B:E and in place Edit>Paste Special>Values>OK>Esc

Delete column A.


Gord Dibben MS Excel MVP

 

[veryeavy]

Hi John,

You might want to try looking at the Data menu and experimenting with "Text
to Columns".

I am not sure what you mean by "every 5th line".

If you meant every fifth character than you could use the "Fixed Width"
option and manually set the "dividers" in the appropriate place.

If you have some particluar character such as a space or a comma marking the
division between the different parts of the address then you may be able to
use the delimited option.

Could you please post a small sample of the data from the "extracted file"?

Cheers,

Matt