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

NameNumberStreettownpostco 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

Try PasteSpecial/Transpose!

Regards,
Stefi


€žPatrick Bateman€� ezt Ã*rta:

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

NameNumberStreettownpostco 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

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:

NameNumberStreettownpostco 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

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:

NameNumberStreettownpostco 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

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:

NameNumberStreettownpostco 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

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:

NameNumberStreettownpostco 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

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