Help

Re: Extracting Text - Close, but not close enough!

Solved
Jump to Solution
1389 0
cancel
Showing results for 
Search instead for 
Did you mean: 
grammy
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

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

Screenshot 2023-01-26 at 1.54.19 PM.png

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

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

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

Screenshot 2023-01-26 at 1.54.19 PM.png

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!