Get text after 3rd space and text before 3rd space

[smartbuyer]

Hi,

I am working with Addresses on this one.

format sample: 1 High Street Sydney NSW 2000

I would like to extract:
"1 High Street" in a different column
"Sydney NSW 2000" in a different column

Appreciate any help

[FlameRetired]

Do you have more samples? What works with the one might not with others.

Edit.
If it's always the 3rd space as your title suggests try this. With the string in A1, this formula in B1 fill across.

Please Login or Register  to view this content.

[smartbuyer]

That works perfect in getting the street address (i.e "1 High Street") !!
How can i get the text string after the 3rd space? (i.e "Sydney NSW 2000")?

What I want is:
A1: 1 High Street Sydney NSW 2000
B1 = 1 High Street
C1 = Sydney NSW 2000

[FlameRetired]

Yes. This formula does that. With the string in A1 enter that formula into B1. Grab the fill handle and fill across to C1.

Row\Col	A	B	C	D
1	1 High Street Sydney NSW 2000	1 High Street	Sydney NSW 2000	In B1: =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99),3),(COLUMNS($A:A)-1)*99+1,99))
2

[smartbuyer]

I didnt think to move the formula to the next column! it works like a charm! thank you

[FlameRetired]

You are welcome. Thanks for the feedback and rep.