Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Extracting Text - Close, but not close enough!

Topic Labels: Community Formulas
Solved
Jump to Solution
1625 2
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!