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]

Thank you very much Singupalli! the addresses with the comma's work perfectly and are translated correctly!

i appreciate all your work a great deal, hopefully the carriage returns wont pose too much of a problem now!

thanks again.

 

[Taniquetil]

right i've tried my hand at visual basic and come to the conclusion that i am technologically retarded.

You guys truely have talent, and i wait eagerly for the carriage returns solution, if not i can always edit the carriages down and add comma's not ideal but still timesaving over all id say!

Regards - Tan