2 row address in 1 cell, how to seperate to 2 cells?

[nzt101]

Hey guys,
my workbook has addresses in one cell, when you click on the pulldown arrow in the text input, it shows it as two lines,
street number street name
suburb, state, post code

I'm trying to make it seperate to two cells so it displays properly in a workbook, at the moment if i reference Cell R1, it shows the address all in one row and bunched up,
for instance
18 StreetnameSTSuburbname,STATE,POSTCODE (no spaces between comma and state/postcode)

whereas ideally i could get it to be like this
18 Streetname ST
Suburb, STATE, POSTCODE

i usually search the forum and google before asking but haven't found an answer to this one yet,
appreciate any help

 

[awoohaw]

Have you explored looking for a zero length text string between the road type and suburb? A carriage return or line feed could do that. If Your address is in cell B2, then do this formula to get the character code:
=CODE(MID(A1,FIND("ST",A1,1)+2,1)), Cap letters are 65-90, Lower case are 97-122.
You can see all the characters in an array by doing Char(SEQUENCE(255,1,1,1)) in O365.
You can split the cell based on the code value in your formula, if it is a 10.. the the delimitter would be char(10).