Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 11, 2022 10:23 AM
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
Solved! Go to Solution.
Sep 11, 2022 01:54 PM
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)
Sep 11, 2022 12:57 PM
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*$"
),
"\/",
""
)
Sep 11, 2022 01:24 PM
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?
Sep 11, 2022 01:54 PM
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)
Sep 11, 2022 03:11 PM
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!
Sep 11, 2022 10:47 PM
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,'')
Sep 12, 2022 03:50 AM
Nice! Even better! :cowboy_hat_face: :raised_hands:
Mar 18, 2023 11:03 AM
I am new at this, but loving the formulas! thanks all!