Transpose Address

[SS]

Hi

I have a file with each part of the address in a separate line down column
A. Sometimes the address could have 4, 5, 6 or 7 lines. How can I
transpose this without having to do each address separately?

I could record separate macros but is there a way to do this that picks up
on the postcode and then transposes the information above that?

Cheers
Shona

 

[Mark E. Philpot]

Hi,
I think you should ensure exactly howmany lines the
address takes up even if an address doesn't need it. In
the old days it looked something like this:

Address1
Address2
City
State
Country
PostCode

Address1 would be for eg: a building name: Melrose Place.
Address2 is always the actual address eg: 42 Melrose Drive


Using this system, you will always know where the postcode
is located.

regards
Mark

 

[SS]

The information for each address was originally written in one cell by
pressing Alt Return and therefore the inputter may have used different
amount of lines.

Cheers
Shona

 

[Andy Brown]

The information for each address was originally written in one cell by
pressing Alt Return and therefore the inputter may have used different
amount of lines.

But each address line *is* in a separate row? And there's at least one blank
row between each address? I can give you an ugly method for this, but can
you say what it's for, given your email address in the context of #16 at
http://www.cpearson.com/excel/newposte.htm ? (no offence intended).

Rgds,
Andy

 

[SS]

Yes each part of the address is now in a different row but unfortunately
there are no blank rows between address.

It's to be used for a mailmerge along with other excel files which have
been set up separately in different formats. Hence the reason to try and
get the information across the columns.

Thanks
Shona
PS to me Newsgroups are a last resort (but a very good one!) as I prefer to
try and do it myself

 

[Andy Brown]

each part of the address is now in a different row but unfortunately

there are no blank rows between address.

Oh, OK. Err, in that case, no, not really. Someone smarter could write you
code to extend a selection downwards until and including the next cell where
the rightmost 2 characters are uppercase. But for that your postcode data
format would have to be 100% reliable.

Assume your data starts in A1. Select A1, then shift-click the first
postcode. Then run

Sub Transpose()
Selection.Copy
ActiveCell.Range("B1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
n = Selection.Cells.Count
ActiveCell.Offset(n, -1).Select
Application.SendKeys "{F8}"
End Sub

After the first run, you'd just need to click the next code and CTRL+Y ;
click the next code and CTRL+Y ; etc. You might want to turn down sheet zoom
the reduce the amount of scrolling.

You'd just have to remember to manually F8 at the end to exit EXT. Then it's
just deleting the blank rows.

HTH,
Andy

 

[Dave Peterson]

If you still have your original data (the addresses are all in one cell
(alt-enters to force a newline within the cell):

Select your column. (Have a lot of blank columns to the right!)
Data|text to columns
delimited
Other character is alt-0010
Hit and hold the alt key, and type 0010 from the numeric keys--not above QWERTY.

Now you've got the single cell split among multiple cells, but if you're putting
each field into its own column, ....good luck!

 

[SS]

Thank you so much so easy when you know how!

Cheers
Shona