LEN ... FIND ...

Hi there! I know there is a way to do this but I keep getting an error!

I have these long zoom links and want to just pull the ID number into a column. I was trying the LEN and FIND functions but not having any luck. The number is always between

https://zoom.us/s/ and ?zak=

The random string of characters after ?zak= is different each time and has varying lengths.

Sample Link:

https://zoom.us/s/98833000000?zak=eyJ0eXAiOiJKV1QiLCJzdiI6IjAwMDAwMiIsInptX3NrbSI6InptXisnlwgfSlwlsaowePwkw90lwghm

Find more posts tagged with

Formulas

Accepted answers

All comments

Darren Mullen

@Jennifer Kaupke

More than one way to do this, but since https://zoom.us/s/ is always the same, I think it would be easiest to use:

=left(Substitute([link column name]@row, "https://zoom.us/s/ ", ""), find("?zak=", Substitute([link column name]@row, "https://zoom.us/s/ ", "") - 1)

You're substituting the standard zoom link prefix with "" and then using left to extract the ID from the result of that based on the location of the ?zak= from that smaller string.

Other option would be to use MID() and staring after the # of characters for "https://zoom.us/s/ " then finding the location of the ?zak and calculating the number of characters from that. It's not impossible, but may more cumbersome.

Jennifer Kaupke

I got an error with that formula but you got me close enough that I was able to work it out the rest of the way. New formula I used:

=LEFT(SUBSTITUTE([Presenter Link]@row, "https://zoom.us/s/", ""), 11)

Thanks!!

Naeem Ejaz

=MID([Zoom Link]@row, FIND ("/s/", [Zoom Link]@row ) + 3, FIND("?zak=", [Zoom Link]@row ) - FIND("/s/", [Zoom Link]@row ) - 3)

Naeem Ejaz

Explanation:

[Zoom Link]@row refers to the cell in the current row that contains the Zoom link.
FIND("/s/", ...) + 3 locates the start of the ID (right after /s/).
FIND("?zak=", ...) finds the end of the ID.
The difference gives the length of the ID to extract.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.