Extracting Text - Close, but not close enough!

Question

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  &#128512;.Address Examples:305 Smith St, COCKBURN SA 544012 Lincoln Cres, WOOLLOOMOOLOO NSW 201112/65 Johnston Lane, DOG SWAMP WA 6060Wanted Outcome:COCKBURN SA 5440WOOLLOOMOOLOO NSW 2011DOG SWAMP WA 6060I 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 Answer

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!

grammy · Answer

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!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded