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.

How to extract text

Topic Labels: Formulas
Solved
Jump to Solution
1357 1
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.