extracting from 1 collumn mailing list

J

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.
 
G

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
 
V

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top