Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 30, 2020 05:55 AM
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?
Solved! Go to Solution.
Sep 30, 2020 07:02 AM
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.
Sep 30, 2020 07:02 AM
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.
Sep 30, 2020 08:01 AM
It doesn’t return anything for me. What is the 13 number for?
Sep 30, 2020 08:13 AM
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.
Sep 30, 2020 08:32 AM
Yes, it did work, I realised the URL in my table was wrong, and have adjusted accordingly!
Sep 30, 2020 08:35 AM
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.