Solved

How to extract text

+2

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

Thanks

Best answer by Ben_Young1

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.

This topic has been closed for replies.

+22

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.

Sign up