Extracting Text - Close, but not close enough!

[grammy]

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!!

[TheTimeSavingCo · Accepted solution]

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

[grammy]

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