Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Extract a part of a link using Regex

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

Hi all,

I would like to extract an ID of a link with Regex formula but I don’t find how this formula works.

Example:

Link Drive (false link)
https://drive.google.com/drive/folders/E1x1a1m1p1le1

And I would like to extract this part: E1x1a1m1p1le1 (i.e. part after “folders/”)

I have tried this formula: IF({Link}, REGEX_EXTRACT({Link}, “folders/[^\s]+”))
but I have the following result : “folders/E1x1a1m1p1le1” instead “E1x1a1m1p1le1”

I guess it should only be a small adjustment.

Could you help me?

Thank you in advance,

Sébastien

1 Solution

Accepted Solutions

Welcome to the community, @Sebastien_C!

I’m not a REGEX expert, but will the first part of the link always be consistent like that? In other words, will everything be consistent up to “folders/”?

If so, then you could do a simple non-REGEX formula like this:

RIGHT({Link},LEN({Link})-FIND("folders/",{Link})-7)

See Solution in Thread

6 Replies 6

Hey @Sebastien_C!

Here’s a regex formula that should return the behavior that you’re looking for:

IF(
    {Name},
    REGEX_REPLACE(
        REGEX_EXTRACT(
            {Name},
            "\\/\\w*$"
        ),
        "\\/",
        ""
    )
)

image

Now, you could omit the parent IF function. However, be aware that if the referenced field is blank, then the formula will throw an error.

image

REGEX_REPLACE(
        REGEX_EXTRACT(
            {Name},
            "\/\w*$"
        ),
        "\/",
        ""
    )

Thank you very much.

It works partially.

Sometimes I have these characters in the links generated by drive: - or _
And if this is the case, it doesn’t work and #error appears

I guess, we would need to change the w*$, is it right?
But maybe we should use a if formula?

Welcome to the community, @Sebastien_C!

I’m not a REGEX expert, but will the first part of the link always be consistent like that? In other words, will everything be consistent up to “folders/”?

If so, then you could do a simple non-REGEX formula like this:

RIGHT({Link},LEN({Link})-FIND("folders/",{Link})-7)

Hi Scott,

Thank you for your suggestion.

I confirm this is always consistent. It seems better than REGEX.

It works well. Thank you very much!

Just for information. This formula does almost the same thing, but it cuts some number of characters at left part, so it can be applied with static number without using FIND() if all the links are the same up to …/folders/

REPLACE({Link},1,39,'')

Nice! Even better! :cowboy_hat_face: :raised_hands: