Text to column formulas with multiple line break delimiters

[CFSJohn]

Hi All, I am looking for some assistance with developing a formula to break up a cell into multiple columns. I have 4 lines to the cell. i need to combine line 1 and 2, break up line 3 into 3 separate cells and then delete the 4th line. I am looking for a formula as I am working on a template so I can drop 13000 lines of data into it and it will break it up for me. the other hiccup is that over the 13000 lines the cell pattern is not always 4 lines. The last line is always Australia. A couple of examples are:

[TABLE="width: 493"]
<tbody>[TR]
[TD="class: xl64, width: 493"]256
Sturt Valley Road
Upper Sturt SA 5156
Australia

1708
Riddoch Highway
Keith SA 5267
Australia
[/TD]
[/TR]
</tbody>[/TABLE]

Aylett Street
Coober Pedy SA 5723
Australia

The format I am looking to create is:
[TABLE="width: 581"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]PAddr1[/TD]
[TD]PAddrSuburb[/TD]
[TD]PAddrState[/TD]
[TD]PAddrPCode[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

I'm a little confused about

the other hiccup is that over the 13000 lines the cell pattern is not always 4 lines

I assume you mean something like your 3rd sample where there's no address #

Since I read something about Australian Postcodes being converted from 4 to 5 digits, and States are Not Always 2 characters, use D1 and E1 formulas which will accommodate Any length Postcode and States.
I've included alternate formulas ( G1 and H1 ) where the Postcode would Always be 4 digits, and the State is Always 2 characters.

Book1
	A	B	C	D	E	F	G	H
1	256 Sturt Valley Road Upper Sturt SA 5156 Australia	256 Sturt Valley Road	Upper Sturt	SA	5156		SA	5156
2	1708 Riddoch Highway Keith SA 5267 Australia	1708 Riddoch Highway	Keith	SA	5267		SA	5267
3	Aylett Street Coober Pedy SA 5723 Australia	Aylett Street	Coober Pedy	SA	5723		SA	5723
Sheet355

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(A1,CHAR(10),REPT(" ",99),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))-1),99),CHAR(10)," "))
C1	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),"Australia",""),B1,""),D1&" "&E1,""))
D1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&"Australia","")," "&E1,"")," ",REPT(" ",99)),99))
E1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&"Australia","")," ",REPT(" ",99)),99))
G1	=RIGHT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&"Australia","")," "&E1,""),2)
H1	=RIGHT(SUBSTITUTE(A1,CHAR(10)&"Australia",""),4)

Formulas copied down.

 

[CFSJohn]

That is awesome and what I was looking to create. I have tried playing with the formulas to do similar with a Postal Address. The cell has the below properties. I have been able to extract the state and postcode, but cant seem to separate the PO Box info from the suburb. The digits for the PO box could be any number between 1 and 2999. Below is an an example of the text

[TABLE="width: 261"]
<tbody>[TR]
[TD="width: 261"] PO BOX 382 Birdwood SA 5234[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

I need to see the PO BOX data/text string as it shows in your cell, with line breaks, if any.
If there's more than 1 variation, show all possible.

 

[CFSJohn]

Hi jtakw,

The Po Box data that I am trying to figure out is

[TABLE="width: 470"]
<tbody>[TR]
[TD="class: xl64, width: 470"] PO BOX 382 Birdwood SA 5234

There is no line breaks in the PO box fields. That was only in the other fields which I have sorted out.[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Ok, your response implies that the Text Strings for PO BOX are completely "Separate" from the text strings as discussed in previous posts.

See how these work for you.

Book1
	A	B	C	D	E
1	PO BOX 382 Birdwood SA 5234	PO BOX 382	Birdwood	SA	5234
2	P.O. Box 382 Birdwood Street SA 5234	P.O. Box 382	Birdwood Street	SA	5234
3	PO BOX 3824 Birdwood SA 5234	PO BOX 3824	Birdwood	SA	5234
4	P.O. Box 2 Birdwood Street SA 5234	P.O. Box 2	Birdwood Street	SA	5234
Sheet363

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1,SEARCH("Box",A1)+4)-1)
C1	=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1&" "&E1,""))
D1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," "&E1,"")," ",REPT(" ",99)),99))
E1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))