LEN ... FIND ...

Jennifer Kaupke

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

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.