Skip to main content
Solved

Extract a part of a link using Regex

  • September 11, 2022
  • 7 replies
  • 67 views
S

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

Best answer by ScottWorld

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)

7 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • September 11, 2022

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*$"
        ),
        "\/",
        ""
    )
S
  • Sebastien_C
  • Author
  • New Participant
  • September 11, 2022

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
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • September 11, 2022

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)

S
  • Sebastien_C
  • Author
  • New Participant
  • September 11, 2022

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!

ScottWorld
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 12, 2022

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
airballer86
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • September 12, 2022

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
Forum|alt.badge.img+18
  • airballer86
  • Known Participant
  • March 18, 2023

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

Terms & ConditionsAccessibility statement