Skip to main content

Hi All,

I need some help with extracting specific text. I've looked through the community and tried to apply the examples offered...but I am not getting it quite right.

I need to extract the suburb, state and post code from a (text) address field. Some suburbs are made up of two names. Below is an example of how the addresses are formatted/written.  Whilst the addresses are made up, these are real suburbs in Australia 😀.

Address Examples:

305 Smith St, COCKBURN SA 5440
12 Lincoln Cres, WOOLLOOMOOLOO NSW 2011
12/65 Johnston Lane, DOG SWAMP WA 6060

Wanted Outcome:

COCKBURN SA 5440
WOOLLOOMOOLOO NSW 2011
DOG SWAMP WA 6060

I appreciate the help. I don't expect an explanation but I am trying to learn so any info alongside the solution will be helpful.

Thank You!!

Hm, if it's always going to be the text after the comma, you could try:

SUBSTITUTE(
Address,
LEFT(
Address,
FIND(
",",
Address
) + 1
),
""
)

We use `FIND()` to get where the comma is in the text, then we use `LEFT()` to extract all the text before the comma, and then we use `SUBSTITUTE()` to replace it all with a blank

Hm, if it's always going to be the text after the comma, you could try:

SUBSTITUTE(
Address,
LEFT(
Address,
FIND(
",",
Address
) + 1
),
""
)

We use `FIND()` to get where the comma is in the text, then we use `LEFT()` to extract all the text before the comma, and then we use `SUBSTITUTE()` to replace it all with a blank


Thank you so much for your time and help. This was what I was after!

Reply


Terms & Conditions