Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Extract a part of a link using Regex

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

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
ScottWorld
18 - Pluto
18 - Pluto

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

7 Replies 7

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*$"
        ),
        "\/",
        ""
    )
Sebastien_C
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

ScottWorld
18 - Pluto
18 - Pluto

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:

airballer86
7 - App Architect
7 - App Architect

I am new at this, but loving the formulas! thanks all!