Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting from 1 collumn mailing list
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting from 1 collumn mailing list
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 EditPaste SpecialValuesOKEsc Delete column A. Gord Dibben MS Excel MVP On Mon, 1 Oct 2007 17:09:02 -0700, JohnW wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting from 1 collumn mailing list
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 "JohnW" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting if data from one collumn is present in another collumn | Excel Worksheet Functions | |||
Mailing list | Excel Discussion (Misc queries) | |||
Need help with mailing list | Excel Discussion (Misc queries) | |||
Mailing List | New Users to Excel | |||
Use of Excel for Mailing List | Excel Discussion (Misc queries) |