Display text from middle of a URL

Hi. I’m currently wondering if there is a way to extract a section of text from a URL.

http://...Drink%2FSeafood%20%2D%20shutterstock_524277715%2Ejpg&...Images%2FFood%20%26%20Drink

I want to Airtable to automate searching the link for “shutterstock_” and then display the following characters up to “%2E”

The link will always have a different amount of characters before “shutterstock_” and after “%2E” and may contain a different length of characters between the two.

Is there a formula for something like this?

I think there are a few ways to get this done, but here’s my solution with the MID() function. Create a new formula field with this formula:

MID(url, SEARCH("shutterstock_", url) + 13, SEARCH("%2E", url) - (SEARCH("shutterstock_", url) + 13))

Replace all 4 instances of “url” with your actual field name that contains the URLs.

Let me know if that works.

1 Like

It doesn’t return anything for me. What is the 13 number for?

The +13 is to account for the 13 characters in “shutterstock_”, because I am trying to count from the end of wherever that string is found, instead of the beginning. I imagine there’s a cleaner way to do that, but this does seem to work for me, as long as the url field is formatted the way you described in the first post.

Just to clarify, in the example you gave, you want the output to be “524277715”, correct?

That’s what I’m seeing when I have one field called “url”, and a second field with the formula I posted above.

I’d be happy to take a look a little deeper though if you want to post screenshots or more details, or send me a PM if you’d rather.

1 Like

Yes, it did work, I realised the URL in my table was wrong, and have adjusted accordingly!

1 Like

Great. I did notice that having “+13” in there can cause a conflict if you ever had a url where “shutterstock_” or “%2E” isn’t found at all, and the output is messed up in that case. This could be solved with an IF/OR, but shouldn’t be a problem as long as the urls follow that format.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.