Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

How to extract text

Topic Labels: Formulas
Solved
Jump to Solution
284 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.