Find Formula - Multiple Instances Of String

[Daniel_Toma]

Hi All,

I have a formula which finds a single instance of a string in a field but I would like it to find multiple instances of the string in the field, how should I change the formula?

MID(
{Field Name},
FIND(
‘beginning of string’,
{Field Name}
)+6,
FIND(
‘end of string’,
{Field Name},
FIND(
‘beginning of string’,
{Field Name}
)
)-(FIND(
‘beginning of string’,
{Field Name}
)+2)
)

[Daniel_Toma]

To clarify, there are multiple strings in a field with the same beginning/ending characters that I want to extract. So XXXabcdeZZZ is one, XXXfghiZZZ is another and XXXjklmZZZ is another. I want to extract: XXXabcdeZZZ XXXfghiZZZ XXXjklmZZZ

[ScottWorld]

Unfortunately, as far as I know, this is not possible to do natively with Airtable’s formula functions.

If you know how to do this in JavaScript, you could write a custom JavaScript script to do this for you.

[Daniel_Toma]

Thanks Scott, appreciate the feedback!

[Justin_Barrett]

There might actually be a way to do this. I came up with a way to extract multiple URLs from an attachment field not long ago, and this feels like it might have similar elements to it.

@Daniel_Toma Would you be able to share an example line from your actual base? Knowing how everything is laid out will let me know how doable this is. The rough example from above doesn’t paint a clear enough picture.

[ScottWorld]

Justin_Barrett:

I came up with a way to extract multiple URLs from an attachment field not long ago, and this feels like it might have similar elements to it.

Nice! That is a pretty cool methodology you figured out there, @Justin_Barrett! And it only has 2 minor requirements, both of which can be easily changed: (1) each URL’s length must be smaller in length than whatever length you specify, and (2) your formula must account for the maximum number of URLs that you expect to have. Very cool! :slightly_smiling_face:

[Daniel_Toma]

Thanks Justin, greatly appreciate it! So basically I have emails (500+ words each) being imported from Gsuite into an Airtable text column - certain emails have multiple hyperlinks included that follow the same format:
“https://xxxxxxxxx.pdf” (all have “https://” at the beginning, “.pdf” at the end). There could be five or six of these links present in a single email.

I created a formula column to extract each separate hyperlink from the email column. The problem with my initial formula is it only extracted the first instance of a hyperlink in a given email, not each separate instance.

[Justin_Barrett]

Thanks for the additional info. Not knowing more about these emails, it’s probably safe to assume that they’re all different enough that finding a pattern in them isn’t going to be nearly as straightforward as it was with the attachment field URL extraction. Thankfully that doesn’t mean we’re out of luck. When a single formula becomes too much of a headache, I switch to using multiple formulas.

The first formula will pad a ton of spaces around each URL, which will allow us to grab each URL as a somewhat arbitrary chunk, then trim the extra spaces off the ends. I named this field {Spaced Out}:

SUBSTITUTE(SUBSTITUTE({Raw Text}, "https", REPT(" ", 100) & "https"), ".pdf", ".pdf" & REPT(" ", 100))

Now we’ll add enough formula fields to extract all the URLs. The first one will search for the first instance. I named this field {File 1} :

TRIM(MID({Spaced Out}, FIND("https", {Spaced Out}), 100))

Each formula field after that finds the next URL using the previous one as a guide. Here’s {File 2}:

IF({File 1}, TRIM(MID({Spaced Out}, FIND("https", {Spaced Out}, FIND({File 1}, {Spaced Out}) + 5), 100)))

{File 3} is the same formula, but using {File 2} as a reference:

IF({File 2}, TRIM(MID({Spaced Out}, FIND("https", {Spaced Out}, FIND({File 2}, {Spaced Out}) + 5), 100)))

And so on through the rest. In the end, you have all the urls extracted in separate fields:

If you have URLs longer than 100 characters (doubtful, but possible), you’ll need to increase the “100” in each of those formulas to something a little larger.

[ScottWorld]

This is pretty amazing, @Justin_Barrett! You are a rock star!!

[Daniel_Toma]

Holy cow that’s awesome and ingenious - thanks so much for the thorough explanation Justin, I really appreciate your help!