Address in excel

[Guest]

Hi, i am pulling through and address from a query which is in the form:

NameNumberStreettownpostcode

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

 

[Guest]

Try PasteSpecial/Transpose!

Regards,
Stefi


„Patrick Bateman†ezt írta:

 

[Guest]

Hi thankyou, but that didnt work, i may not have explained properly, all the
address information is in one cell.

regards

Patrick

 

[Guest]

Then first separate them with Data/Text to columns!

Regards,
Stefi


„Patrick Bateman†ezt írta:

 

[Guest]

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

 

[Guest]

You have to write a VBA macro for this purpose!

Regards,
Stefi


„Patrick Bateman†ezt írta:

 

[Guest]

Some thoughts ..

Assuming you have split (using Data>Text 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


---