Exclude starting zero's from the middle of a value

[Shawntel_Cote]

hi! I have a field that is manually entered and I’m trying to create a formula field that extracts the middle number (found between “-” and “.”) but excludes the starting zero’s. This middle number can range from 1 to 999. I’ve tried a variety of Right/Left & MID, but I can’t seem to exclude the starting zero’s.

Examples:
123456-002.5 → “2”
154773-025.26 -->“26”
89347-031.124 → “31”

[Ben_Young1]

Hey @Shawntel_Cote!

Truth be told, with the exception of a couple of fringe uses, I never use the RIGHT/LEFT/MID functions for extracting strings.

The sequencing and nesting of the functions have never quite agreed with my brain, so regex is my preferred tool to tackle things like this.

Here are the example strings you provided, along with the formula results.
Here’s the raw form formula that I wrote:

IF(
    {Name},
    REGEX_EXTRACT(
        REGEX_EXTRACT(
            {Name},
            '-\d+.'
        ),
        '[1-9]+'
    )
)

The formula works like this:

• If the {Name} field returns a value, then…

  • Extract anything between the ‘-’ and the ‘.’
    This will leave us with an example value of -031.

  • From that string, extract any numbers in the range of [1-9].
    Since we didn’t include 0 in the range, it will ignore any zeros. For reference, this range would return all numerical digits [0-9].

  • The + you see at the end of [1-9]+ is a quantifier. That is to say that we’re clarifying the search parameter/pattern to say, “Hey, if you find one or more of these, then include them.”

All of this leaves us with a final extracted value of 31 from an original value of 89347-031.124.

[kuovonne]

Shawntel_Cote:

Examples:
123456-002.5 → “2”
154773-025.26 -->“26”
89347-031.124 → “31”

It looks like you have a typo in your example. Should the middle example be 25 instead of 26? If this isn’t a typo, this is a much more complex situation.

Ben.Young:

Extract anything between the ‘-’ and the ‘.’

You may want to escape the . in your expression. A period in a RegEx can be interpreted as any character and not necessarily a literal period. In this case, it probably won’t make a difference since the expression is greedy, but it is still good practice.

Ben.Young:

From that string, extract any numbers in the range of [1-9].

This is a bit dangerous because it will remove all zeros, not just leading zeros. It also will return only the digits before any internal or trailing zeros, because REGEX_EXTRACT only returns the first match, not all matches. If the number haves internal or trailing zeros, you will get the wrong result. For example “123456-010.5” should return 10, and not 1. This is why it is important to have robust test cases.

---

It also isn’t clear if @Shawntel_Cote wants the final result to be a text string that looks like a number, or an actual number that math can be performed on.

I propose this variation:

IF(
    REGEX_MATCH(
            {Name},
            '-\\d+\\.'
    ),
    ABS(VALUE(
        REGEX_EXTRACT(
            {Name},
            '-\\d+\\.'
        )
    ))
)

[Ben_Young1]

kuovonne:

In this case, it probably won’t make a difference since the expression is greedy, but it is still good practice.

Lol yeah, it’s just me being rebellious about all the weird regex behavior in Airtable.
I’m away from my keyboard, so I can’t test it, but will Airtable even let you escape the period?

kuovonne:

remove all zeros, not just leading zeros

Good catch!
For the sake of putting it into the forum history, I’ll toss a string datatype variation:

IF(
    {Name},
    REGEX_EXTRACT(
        REGEX_EXTRACT(
            REGEX_EXTRACT(
                {Name},
                '-\\d+.'
            ),
            '[0-9]+'
        ),
        '[^0]+?[1-9]*\d*'
    )
)

Excuse the mobile screenshot, I’m traveling lol.