Display text from middle of a URL

[Antony_Bearpark]

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?

[Nick_Dennis · Accepted solution]

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.

[Antony_Bearpark]

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

[Nick_Dennis]

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.

[Antony_Bearpark]

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

[Nick_Dennis]

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.