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:

Suburb	State	Postcode
Surry Hills	NSW	2011
Maroubra	VIC	3041

<tbody>
</tbody>

At the moment however, it looks like this:

Address
Beverly Hills NSW 2076
Bondi ACT 5607

<tbody>
</tbody>

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

Address
Beverly	Hills	NSW	6033

<tbody>
</tbody>

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:

Suburb	State/Postcode
Alexandria	NSW 1092
Surry Hills	VIC 1039

<tbody>
</tbody>

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?