Help

Re: Formula for cutting out numeric values

Solved
Jump to Solution
2126 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Colina1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there! This is a very newbie question, but is there a formula for cutting numbers out of a string? For example, if I have the value

01 Lorem ipsum

and I want it to read

Lorem ipsum

what formula would I use?

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

The T() function is used to see if the input is text. It won’t convert a text string.

Try this formula. It will only strip out numbers at the beginning of the the text string (and the single space after those numbers). If there are numbers mixed in with the text, they will remain.

REGEX_REPLACE({text}, "^\\d* ", "")

See Solution in Thread

11 Replies 11

Hey @Josh_Colina1!

You’ll want to utilize the T() function.

For example:

T(
    "01 Lorem ipsum"
)

Would return:
Lorem Ipsum

You can find more information here.

Let me know if you have any additional questions or are curious to explore a bit more!


Edit:

Okay, I actually might’ve just lied to you lol.
I’m testing the function right now to confirm that’s what you’ll want to use.
I will return with a confirmation or a correction in a moment.

Okay, I was wrong.

That function will only return a value if the entire string does not contain a numeric value.

This formula will work instead:

TRIM(
    REGEX_REPLACE(
        REGEX_REPLACE(
            {String},
            '\d',
            ''
        ),
        '\s{2,}',
        ' '
    )
)
kuovonne
18 - Pluto
18 - Pluto

The T() function is used to see if the input is text. It won’t convert a text string.

Try this formula. It will only strip out numbers at the beginning of the the text string (and the single space after those numbers). If there are numbers mixed in with the text, they will remain.

REGEX_REPLACE({text}, "^\\d* ", "")

Hey @kuovonne, I’ve never actually used the T() function before.
Now that I’m looking at the syntax and accepted parameters, I can’t think of a use case where it would come in handy.

Curious if you’ve ever used it before or know where it could be used?

No worries at all, really appreciative for the input!

Thank you very much! I’ll take this for a spin - I was experimenting with REGEX_REPLACE but couldn’t quite figure out the configuration :face_with_spiral_eyes: -could you help me understand (mostly for posterity) how you wrote this formula?

EDIT:
This worked great!

The ^ says to look for the start of the string.
The \\d* says to look for zero or more digit characters. (The extra slash is something Airtable sticks in.)
The space looks for a literal space.

Then replace that whole thing with an empty string.

I haven’t found a use case for T() yet. I just looked at the documentation and played with it a bit. I think it is a legacy formula that someone thought would be useful a long time ago but isn’t really really useful now that we have more robust formula functions.

I’m going to jump in for my formula. I love regex.

If you’re just getting your feet wet in it, I always recommend that you spend more time planning than you do typing.
Determine what you’re trying to do before you try and do it.


Let’s take this example string:
1 This is my first log entry. The temperature today is 34 degrees.
We want to get every numeric value out of a string, leaving everything else.

We now need to create what’s called a pattern.
Think of this as the rulebook that we want regex to play by.

The pattern I gave it is: \d
For regex, \d equates to every number from 0 to 9.

Another way we can call out digits is by telling regex to look for a range of characters.
For digits, this can be written as [0-9]. This will look for 0, 9, and everything in-between.

Ranges also work for alphabet characters.

  • [a-z] = Lowercase alphabet characters.
  • [A-Z] = Uppercase alphabet characters.

You can also set multiple ranges in a single bracket group.

  • [a-zA-Z]
  • [a-zA-Z0-9]

REGEX_REPLACE()

For our Airtable formula, we will use the REGEX_REPLACE() function.

The syntax for this function is:
REGEX_REPLACE(string, regex_pattern, replacement)

The first parameter is the string we want to evaluate.
The second is the ‘playbook’ that we want regex to follow.
The third is what we want to replace the characters that the regex captured.

In Airtable, we have our record and field containing our string.

image

We’ll start filling out our formula. With this {String} field, we get:

REGEX_REPLACE(
    {String},
    regex_pattern,
    replacement
)

Next, we’re going to provide the function with the regex pattern or ‘playbook’ to use in its search.
We now get:

REGEX_REPLACE(
   {String},
   '\d',
    replacement

For our last parameter, we need to tell the function what we want to replace whatever the function finds.
To do this, we can just provide the expression of ''.
Note that there are no spaces between those quotation marks. This equates to blank. This is distinct from ' ', which will insert whitespace as a replacement.

Here’s the final behavior:

image

Now, you will notice a strange behavior in the screenshot above. Even though we removed the numbers, there is an extra space between the words where the number(s) used to be.

This happens because the number(s) had spaces before and after the words they were between.

Let’s fix this.

To do so, we can actually utilize a new REGEX_REPLACE() function.
In this case, we’ll want to find every instance where consecutive spaces exist.
If found, we want to replace them with just a single space.

In regex, \s equates to whitespace.
But this won’t cut it for what we want to do. We want to find every instance where there are two or more next to each other.

Sure, we could just say \s\s, but what if there are three? If we give it \s\s\s, then it won’t catch when there are only two.

This is where we can use what’s called a quantifier.
This is how we tell regex when to look for a certain number of things.

There are a ton of different and confusing ways to utilize quantifiers.
In our case, we’ll want to use {2,}.
In regex, the {x,} quantifier will look for (x) number or more of something.
e.g.

  • {4,} → Four or more.
  • {5,} → Five or more, etc.

If we write \s{2,}, we’re saying that we want to capture any time there are two or more spaces next to each other.

When put into a REGEX_REPLACE() function, it looks like this:

REGEX_REPLACE(
    {string},
    '\s{2,}`,
    ''
)

For our string, we want to use the output from our first REGEX_REPLACE() formula, so we’ll just nest it like this:

REGEX_REPLACE(
    REGEX_REPLACE(
        {String},
        '\d',
        ''
    ),
    '\s{2,}',
    ' '
)

We now have this:

image

There’s one last piece to this formula.
It might even be readily apparent.

Because there was a number at the beginning of the string, there’s actually a leading space at the start of the formula’s output.

This is what it looks like:

'  This is my first log entry. The temperature today is degrees.'

Fixing this is actually quite simple.
We can fix this using the TRIM() function.
This function removes all leading and trailing whitespace in text strings.

All we have to do is wrap our entire formula inside the TRIM() function like this:

TRIM(
    REGEX_REPLACE(
        REGEX_REPLACE(
            {String},
            '\d',
            ''
        ),
        '\s{2,}',
        ' '
    )
)

This is the final result:

image


I highly recommend playing around with Regex101 to see how changing the patterns and strings will return different results as you tweak things.

It’s a great learning tool. I would never have been able to get my head around regex without it!


This is an important distinction between Ben’s formula and mine. Mine only removes digits at the start of the string. Ben’s removes all digits everywhere in the string.

Note that this can be problematic. What if there are two or more spaces next to each other that were in the original string that did not have digits between them? This formula will remove those additional spaces as well.

There are a few other options. Instead of using '\s{2,}' you could also use ' +' to find one or more spaces and replace them with a single space. This would have the same effect. It depends on what you find easier to read and how much work you want the computer to use.

However, another option would be when searching for digits to search for one or more digits and zero or more trailing spaces in the initial REGEX_REPLACE: '\d+ *. This would leave any consecutive spaces that were supposed to be there unaffected.

I agree. A regex tester is essential to learning regex. However, I find that different regex testers have different helps, so if one doesn’t work for you, try a different website.

There are many ways to do things in REGEX. Testing with a wide variety of inputs is essential to getting the result you want.

How does one also extract the numbers that are mixed with texts in the middle?