Need formula to break up column

[Sm3tUser]

I posted yesterday, but I'm back with another issue. I need my document to look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Suburb[/TD]
[TD]State[/TD]
[TD]Postcode[/TD]
[/TR]
[TR]
[TD]Surry Hills[/TD]
[TD]NSW[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]Maroubra[/TD]
[TD]VIC[/TD]
[TD]3041[/TD]
[/TR]
</tbody>[/TABLE]


At the moment however, it looks like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Address[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beverly Hills NSW 2076[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bondi ACT 5607[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can't use Text to Columns because then it would split suburbs that have two words up as well. Eg.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beverly[/TD]
[TD]Hills[/TD]
[TD]NSW[/TD]
[TD]6033[/TD]
[/TR]
</tbody>[/TABLE]


So, I need a formula that follows the following conditions:


- If the cell has 4 words/pieces of text, then split the column after the second space
- If the cell has 3 words/pieces of text, then split the column after the first space


It should look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Suburb[/TD]
[TD]State/Postcode[/TD]
[/TR]
[TR]
[TD]Alexandria[/TD]
[TD]NSW 1092[/TD]
[/TR]
[TR]
[TD]Surry Hills[/TD]
[TD]VIC 1039[/TD]
[/TR]
</tbody>[/TABLE]


Afterwards, I will just use Text to Columns to split up the State/Postcode box, so no need to worry about that. The end result should be the first table in my post.


I appreciate any and all help - thanks!

 

[jasonb75]

Try

Suburb =LEFT(A1,LEN(A1)-9)
State =LEFT(RIGHT(A1,8),3)
Postcode =RIGHT(A1,4)

 

[BenMiller]

Too lazy to write a shorter formula.

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))-1))-1)

 

[Sm3tUser]

Thanks for the reply, what box would I put it into? I need it to space over all three columns

 

[jasonb75]

Thanks for the reply, what box would I put it into? I need it to space over all three columns

You received 2 answers, which is that question aimed at?