Extract a part of a link using Regex

[Sebastien_C]

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

[Ben_Young1]

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*$"
        ),
        "\\/",
        ""
    )
)

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.

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

[Sebastien_C]

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 · Accepted solution]

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)

[Sebastien_C]

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!

[Alexey_Gusev]

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,'')

[ScottWorld]

Alexey_Gusev:

REPLACE({Link},1,39,‘’)

Nice! Even better! :cowboy_hat_face: :raised_hands:

[airballer86]

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