Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi, i am pulling through and address from a query which is in the form:
NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Try PasteSpecial/Transpose!
Regards, Stefi Patrick Bateman ezt *rta: Hi, i am pulling through and address from a query which is in the form: NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi thankyou, but that didnt work, i may not have explained properly, all the
address information is in one cell. regards Patrick "Stefi" wrote: Try PasteSpecial/Transpose! Regards, Stefi Patrick Bateman ezt *rta: Hi, i am pulling through and address from a query which is in the form: NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Then first separate them with Data/Text to columns!
Regards, Stefi Patrick Bateman ezt *rta: Hi thankyou, but that didnt work, i may not have explained properly, all the address information is in one cell. regards Patrick "Stefi" wrote: Try PasteSpecial/Transpose! Regards, Stefi Patrick Bateman ezt *rta: Hi, i am pulling through and address from a query which is in the form: NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
this partly works when i do it but is it possible for it to be done
automatically as each different address is pulled through from the query? regards patrick "Stefi" wrote: Then first separate them with Data/Text to columns! Regards, Stefi Patrick Bateman ezt *rta: Hi thankyou, but that didnt work, i may not have explained properly, all the address information is in one cell. regards Patrick "Stefi" wrote: Try PasteSpecial/Transpose! Regards, Stefi Patrick Bateman ezt *rta: Hi, i am pulling through and address from a query which is in the form: NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You have to write a VBA macro for this purpose!
Regards, Stefi Patrick Bateman ezt *rta: this partly works when i do it but is it possible for it to be done automatically as each different address is pulled through from the query? regards patrick "Stefi" wrote: Then first separate them with Data/Text to columns! Regards, Stefi Patrick Bateman ezt *rta: Hi thankyou, but that didnt work, i may not have explained properly, all the address information is in one cell. regards Patrick "Stefi" wrote: Try PasteSpecial/Transpose! Regards, Stefi Patrick Bateman ezt *rta: Hi, i am pulling through and address from a query which is in the form: NameNumberStreettownpostco de and i need it to populate cells in the form: Name Number Street town Postcode any ideas how? any help would be much appreciated regards Patrick |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Some thoughts ..
Assuming you have split (using DataText to Columns) the imported data into 5 cols in Sheet1's cols A to E, from row1 down, eg: Name1 Number1 Street1 town1 postcode1 Name2 Number2 Street2 town2 postcode2 Name3 Number3 Street3 town3 postcode3 etc Then in Sheet2, Put in A1: =OFFSET(Sheet1!$A$1,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5)) Copy A1 down as far as required, until zeros appear signalling exhaustion of data in Sheet1. Col A will return it in the desired single column "repeating" format, viz: Name1 Number1 Street1 town1 postcode1 Name2 Number2 Street2 town2 postcode2 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
excel problem: sum(address(4,2,1):address(2,1,1)) | Excel Discussion (Misc queries) | |||
what is difference between absolute address and relative address? | Excel Discussion (Misc queries) | |||
long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) | |||
How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) |