Address Line Separation! bane of my life!

[Taniquetil]

Hi,

great site, been an observer for a long time.

Having alot of problems with address line separation and cant find any answers unfortunately.

The two addresses i come across are.

Line1
Line2
Line3 (occasionally)
City
County
P0S CDE

or

Line1, Line2, Line3 (occasionally), City, County, P0S CDE

my question is there any way to make a formula to organise these in to separate cells in a row as follows. <> = new cell

Line 1 <> Line2 <> Line3 <> City <> County <> P0S CDE

i know there are sometimes lines missing aswell, usually the line3 of address, if possible could there be a formula to cause that to be the blank cell when there is not enough data? (Im a dreamer i know! ) of course i can still do this manually if not

Regards, my headache will thank you for eternity!

 

[Taniquetil]

Bane of my life

 

[SuperFerret]

As an example:
=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4&CHAR(10)&A5&CHAR(10)
&A6
Then set the cell to Wrap Text to get them with carriage returns
=SUBSTITUTE(C1,CHAR(10),", ")
To get them with carriage returns to a single line

Is this the sort of thing you need or are you needing to seperate into individual cells

Line 1 in A1 Line 2 in B1 etc?

 

[Taniquetil]

yes i need them in individual cells unfortunately.

Line 1=A1, Line2=A2 etc

thank you for the help though, i am distinctly amateur at this and all help is greatly appreciated!

 

[SuperFerret]

Right, let me have a ponder about this.

Is VBA ok?

 

[Taniquetil]

excuse the ignorance but i dont know what VBA is, if it works it's most definitely ok!

 

[singupalli]

1 Line1
Line2
Line3 (occasionally)
City
County
P0S CDE

2 Line1
Line2

City
County
P0S CDE

Result 1 Line1 Line2 Line3 City County P0S CDE
Result 2 Line1 Line2 0 City County P0S CDE

is this what u want

 

[singupalli]

Line1 Andrew
Line2 paul
Line3 Symonds
City Sydney
County Aus
P0S CDE 34345

Line1 paul
Line2 Symonds

City Melbrne
County Aus
P0S CDE 65656

Result Line1 Line2 Line3 City County P0S CDE
Andrew paul Symonds Sydney Aus 34345
paul Symonds 0 Melbrne Aus 65656

 

[SuperFerret]

OP wants:
<TABLE style="WIDTH: 208pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=277><COLGROUP><COL style="WIDTH: 208pt; mso-width-source: userset; mso-width-alt: 10130" width=277><TBODY><TR style="HEIGHT: 90pt" height=120><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 208pt; HEIGHT: 90pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=120 width=277>Line 1
Line 2
Line 3
City
County
POS CDE
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20>Line 1, Line 2, Line 3, City, County, POS CDE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20>Line 1, Line 2, City, County, POS CDE</TD></TR><TR style="HEIGHT: 90.75pt" height=121><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 208pt; HEIGHT: 90.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=121 width=277>Line 1
Line 2

City
County
POS CDE
</TD></TR></TBODY></TABLE>

To become:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=64>Line 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>County</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>POS CDE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>County</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>POS CDE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>County</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>POS CDE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>Line 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>City</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67 height=20 width=64>County</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=21 width=64>POS CDE</TD></TR></TBODY></TABLE>

 

[Taniquetil]

1 Line1
Line2
Line3 (occasionally)
City
County
P0S CDE

2 Line1
Line2

City
County
P0S CDE

Result 1 Line1 Line2 Line3 City County P0S CDE
Result 2 Line1 Line2 0 City County P0S CDE

is this what u want

Does this require the address lines to be posted in individual cells in the first place, as most of them are currently all in one cell.
the whole address in cell A1 for example.

The eventual layout is exactly what i need

also will this work when the address is sent to me laid out as

park lane, littleton, townsville, oxon, P0S CDE (all in one box with comma separators)

Thank you for all the help! there is light at the end of the tunnel!