This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Formula to get image attachment url

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
3030
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 25, 2018 06:47 AM

I have an attachment field containing images. I see that the stored attachment object has a url property. In another formula field I would like to show just the attachement url. Any ideas?

Thanks.

Solved! Go to Solution.

Reply

2 Solutions

Accepted Solutions

Solved
See Solution in Thread

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Not the Solution
- Report Inappropriate Content

Jun 29, 2020 12:00 PM

While this could technically be done in a single formula, it would be a *massive* formula. In cases like this, I prefer to divide and conquer, splitting the effort across more than one formula field.

To begin, I’ll take the default output from the attachment field and spread things out a bit by replacing the parentheses around the URLS with large blocks of spaces. I’ll also prepend a number that tells me how many attachments exist, which I’ll use later. This is a formula field that I named `{Expanded}`

:

```
IF(
Attachment,
((LEN(Attachment) - LEN(SUBSTITUTE(Attachment, ",", ""))) + 1)
& " - "
& SUBSTITUTE(SUBSTITUTE(Attachment, "(", REPT(" ", 100)), ")", REPT(" ", 100))
)
```

That creates the following output (shown in the field’s expanded view, and based on a three-attachment field):

The URL extraction then becomes much easier. Each URL is grabbed in a 200-character block using the `MID()`

function, and the surrounding spaces are trimmed off with the `TRIM()`

function. In my test, I found that I could grab the first URL block from my expanded string starting at the 100th character, the second at the 400th, and the third at the 700th. If you have really long filenames, these starting positions may need slight adjustments. For separation between each URL in the final output, I just used commas, but you can change that as desired. Anyway, here’s the final formula, which I put into a field named `{URLs}`

.

```
IF(
Expanded,
TRIM(MID(Expanded, 100, 200))
& IF(LEFT(Expanded, 1) > 1, ", " & TRIM(MID(Expanded, 400, 200)))
& IF(LEFT(Expanded, 1) > 2, ", " & TRIM(MID(Expanded, 700, 200)))
)
```

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Not the Solution
- Report Inappropriate Content

Jun 30, 2020 09:26 AM

For more than three attachments, keep expanding the second formula. Notice the pattern in the `MID()`

functions:

It starts by grabbing characters beginning at position 100…

…then position 400, then position 700. In short, we’re adding 300 each time to grab the next URL. Just keep that pattern going. The next one would start at position 1000, then 1300, then 1600, etc.

Also notice the pattern in the comparisons made before grabbing each subsequent piece. Each line increases the number we’re comparing against from the start of `{Expanded}`

. The first one is just grabbed outright because we know we have at least one URL. If we have more than one—`IF(LEFT(Expanded, 1) > 1`

—we grab the next piece. If more than 2, grab the next one. Keep that pattern going as well.

Also, for more than 9 attachments, you would need to change all instances `LEFT(Expanded, 1)`

to `LEFT(Expanded, 2)`

.

A lot of programming is about finding patterns. Learn to see the patterns in the data you’re playing with, and you can write code to find things based on those patterns.

Reply

12 Replies 12

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Feb 14, 2019 05:25 PM

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Feb 14, 2019 07:00 PM

The following formula will give you the URL for an attachment field :slightly_smiling_face:

`RIGHT(LEFT({Attachment Field}, LEN({Attachment Field}) - 1), LEN(LEFT({Attachment Field}, LEN({Attachment Field}) - 1)) - SEARCH("https://", {Attachment Field}) + 1)`

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Dec 08, 2019 01:16 PM

Hey, is there a way to get only the urls separated by coma, without the file names?

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Dec 08, 2019 01:37 PM

The formula that @AlliAlosa posted will work for a single attachment. With multiple attachments, the process would be much more complex because the filenames and URLs are interspersed. You can’t write a single formula that would extract the URLs from an arbitrary number of attachments. For example, if you write a formula (or series of formulas) that can extract up to five URLs, you’ll have to revisit the code the moment you have more than five attachments.

What I suggest doing is using the sample that @AlliAlosa provided to learn how the first URL was extracted. To get the second URL, search for it using the index of the first one as a reference. Same for the third, and so on.

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jan 09, 2020 10:41 PM

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jan 27, 2020 09:51 AM

Hello!.. I saw your video and is amazing!!!, great work. I’ll boy some of your extensions.

Nick

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jun 28, 2020 07:18 PM

Hello,

Thanks for that!

Would you be able to make a formula that gets up to 3 URLs if there’s up to 3 attachments in the attachment field?

I’m trying but I get confused with all the left, len, right…

Accept as Solution

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Not the Solution
- Report Inappropriate Content

Jun 29, 2020 12:00 PM

While this could technically be done in a single formula, it would be a *massive* formula. In cases like this, I prefer to divide and conquer, splitting the effort across more than one formula field.

To begin, I’ll take the default output from the attachment field and spread things out a bit by replacing the parentheses around the URLS with large blocks of spaces. I’ll also prepend a number that tells me how many attachments exist, which I’ll use later. This is a formula field that I named `{Expanded}`

:

```
IF(
Attachment,
((LEN(Attachment) - LEN(SUBSTITUTE(Attachment, ",", ""))) + 1)
& " - "
& SUBSTITUTE(SUBSTITUTE(Attachment, "(", REPT(" ", 100)), ")", REPT(" ", 100))
)
```

That creates the following output (shown in the field’s expanded view, and based on a three-attachment field):

The URL extraction then becomes much easier. Each URL is grabbed in a 200-character block using the `MID()`

function, and the surrounding spaces are trimmed off with the `TRIM()`

function. In my test, I found that I could grab the first URL block from my expanded string starting at the 100th character, the second at the 400th, and the third at the 700th. If you have really long filenames, these starting positions may need slight adjustments. For separation between each URL in the final output, I just used commas, but you can change that as desired. Anyway, here’s the final formula, which I put into a field named `{URLs}`

.

```
IF(
Expanded,
TRIM(MID(Expanded, 100, 200))
& IF(LEFT(Expanded, 1) > 1, ", " & TRIM(MID(Expanded, 400, 200)))
& IF(LEFT(Expanded, 1) > 2, ", " & TRIM(MID(Expanded, 700, 200)))
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jun 30, 2020 07:30 AM

Thanks for your Formula !

What would it be with more than 3 attachments ? For example 10 attachments ?

Accept as Solution

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Not the Solution
- Report Inappropriate Content

Jun 30, 2020 09:26 AM

For more than three attachments, keep expanding the second formula. Notice the pattern in the `MID()`

functions:

It starts by grabbing characters beginning at position 100…

…then position 400, then position 700. In short, we’re adding 300 each time to grab the next URL. Just keep that pattern going. The next one would start at position 1000, then 1300, then 1600, etc.

Also notice the pattern in the comparisons made before grabbing each subsequent piece. Each line increases the number we’re comparing against from the start of `{Expanded}`

. The first one is just grabbed outright because we know we have at least one URL. If we have more than one—`IF(LEFT(Expanded, 1) > 1`

—we grab the next piece. If more than 2, grab the next one. Keep that pattern going as well.

Also, for more than 9 attachments, you would need to change all instances `LEFT(Expanded, 1)`

to `LEFT(Expanded, 2)`

.

A lot of programming is about finding patterns. Learn to see the patterns in the data you’re playing with, and you can write code to find things based on those patterns.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jul 01, 2020 01:00 AM

Thank you so much. You save my day ! Good Work and clear explanations.

Accept as Solution

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Accept as Solution
- Report Inappropriate Content

Jul 02, 2020 01:49 AM

Thanks so much, you have saved me hours of work!

Accept as Solution

Reply