Formula for multiple image URL split into multiple columns

[Jarvis]

Hello, this is more like a documentation rather than a question, for those who might find it helpful and have similar use cases. If there are errors in the formula, please point them out.

I have an attachment field called Picture. The number of pictures I’ll have in the attachment field can vary from 1 to 20. I want to grab the URL of each image, and put them into multiple columns. To do this, I’ll have to create 20 additional columns, one for each field. Note that this works as long as the image names don’t have any brackets in them. Hopefully, this won’t be a problem for many.

The logic behind the formula is as follows:

• I have an attachment field called Picture
• I create a formula field called URL beside it, and the formula in it is {Picture}. This returns the URL of all the images in the Picture field, along with the image names. This is the field I’ll try to clean up and extract the individual image urls from

First, I’ll use this formula to find the first “(” in the URL field:
FIND("(",URL)

Then, I’ll use this formula to find the first “)” in the URL field:
FIND(")",URL)

Then, I’ll calculate the length between these two brackets in the URL field:
{Find 1st )} - {Find 1st (} - 1

I’ll create a field called URL 1. I’ll use this above value to extract that part from the URL field, starting 1 character after the first “(”:
MID(URL, {Find 1st (} + 1, {between ( and )})

There, first image URL has been cleanly extracted. Now for the 2nd image:

I’ll use this formula to find the second “(” in the URL field:
FIND("(", URL, {Find 1st (} + 1)

Then I’ll use this formula to find the second “)” in the URL field:
FIND(")", URL, {Find 1st )} + 1)

Then I’ll find the difference in characters between these two brackets:
{Find 2nd )} - {Find 2nd (} - 1

And use the MID formula to find the 2nd URL
MID(URL, {Find 2nd (} + 1, {between 2nd ( and )})

While this looks way too complicated, making the formula was actually quite simple because I first split all the parts into individual columns, and then later combined them after they were all created. Here’s a screenshot of my base.

Instead of wasting time, you can simply copy/paste the formulas I’ve put in below. Make sure to name your attachment field Picture, and then create a formula field called URL with formula {Picture}. Then, create 20 additional formula fields titled URL 1 to URL 20. Copy and paste the below formulas for each column.

URL 1: MID(URL,FIND("(",URL)+1,FIND(")",URL)-FIND("(",URL)-1)
URL 2: MID(URL,FIND("(",URL,FIND(")",URL)+1)+1,FIND(")",URL,FIND(")",URL)+1)-FIND("(",URL,FIND(")",URL)+1)-1)

I’ll update this post once I’ve finished making the formulas for all the columns :stuck_out_tongue:

[Josh_Doyle]

Thank you for documenting this solution!!

[Jarvis · Accepted solution]

Here’s the full excel file for all 20 fields. It also contains the inner workings that I used to generate the list:

drive.google.com

URL column formula.xlsx

Google Drive file.

It contains all the fields and the corresponding formulas that you’ll need to make for this to work.

I’ve noticed that this system breaks down if there’s a comma or a bracket in the file name