Help

Re: How to extract text

Solved
Jump to Solution
612 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael1
6 - Interface Innovator
6 - Interface Innovator

Hi, I have a list of cities and states, and I want to extract only first state initials. How can I do that?

image

Thanks

1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

Hey @Michael1!

I had a similar use case a long time ago.
Here’s the formula I used back then:

(I added in your field name {City/States})

TRIM(
    SUBSTITUTE(
        REGEX_EXTRACT(
            {City/States},
            "(,\s..)"
        ),
        ",",
        ""
    )
)

Now, there is a something important to note.
This (specific) formula only works if you have a comma and a space before the state code.

This requires that you must have consistent and clean data in order for the formula to be consistent.

See Solution in Thread

1 Reply 1
Ben_Young1
11 - Venus
11 - Venus

Hey @Michael1!

I had a similar use case a long time ago.
Here’s the formula I used back then:

(I added in your field name {City/States})

TRIM(
    SUBSTITUTE(
        REGEX_EXTRACT(
            {City/States},
            "(,\s..)"
        ),
        ",",
        ""
    )
)

Now, there is a something important to note.
This (specific) formula only works if you have a comma and a space before the state code.

This requires that you must have consistent and clean data in order for the formula to be consistent.