Extract Text From Between Two Set Characters

Hi!

This one seems quite easy, but having a tough time to loop LEFT and RIGHT to make it work …

I have the following examples
5-DBR_4.JPG
5-GD-WHI_1.jpg

In both instances, I want to extract the text between the “_” and the “.JPG”, so “4” and “1” respectively.
This is to populate an {Position} field.

What is the right formula to use for this?

Howdy -

@Melanie_E_Magdalena and I had a crack at this for you - not sure how variable your data is, but with the samples you gave, try this:

LEFT(RIGHT(Name,5),1)
where {Name} = the field name

Hope that helps!!

Grant

1 Like

If you can’t be sure of the relative position of the number — for instance, if your filetype suffix might be ‘jpg’ or ‘jpeg’, or if the position text might be more than a single digit — but you know the relative locations of the underscore and period stay the same, you can use MID() with more confidence:

MID(
    {Text},
    FIND(
        '_',
        {Text}
        )+1,
    FIND(
        '.',
        {Text},
        FIND(
            '_',
            {Text}
            )
        )-(FIND(
            '_',
            {Text}
            )+1)
    )

That will extract a string of any length between the first '_' in {Text} and the first '.' in {Text} that falls after the "_".

That is,

5-DBR_32.jpg will return 32
5-GD-WHI_10011.jpeg will return 10011

and

36-LT.BLUE_8.wbem will return 8.

However,

6-DRK_RED_7.GIF will return RED_7

– embedded underscores will trip up the formula.

3 Likes

THANK YOU!!!
This is an AMAZING find. I am slow with airtable formulas as a rule - but this got me over a hump I’d been working around for 2 weeks. Specially, how to extract the web url of the attachment field (an image in my case) that does not contain the parentheses. Testing it now but positive results so far

“parent_file (https://dl.airtable.com/.attachments/b85ad854f7d480569ea1a020c00f8792/a6ba6ba112/parent_file)”

I modified the formula above (my column is called “attachment_url” ) and it worked like a charm
My method was to

  1. add an interim formula column (sloppy I know) that pulled the full text of the attachment URL into it - “parent_file (https://dl.airtable.com/.attachments/b85ad854f7d480569ea1a020c00f8792/a6ba6ba112/parent_file)”
  2. add a second formula column that looked for stuff between the parentheses and after the first parentheses
    MID(
    {attachment_url},
    FIND(
    ‘(’,
    {attachment_url}
    )+1,
    FIND(
    ‘)’,
    {attachment_url},
    FIND(
    ‘(’,
    {attachment_url}
    )
    )-(FIND(
    ‘(’,
    {attachment_url}
    )+1)
    )

the result?
https://dl.airtable.com/.attachments/b85ad854f7d480569ea1a020c00f8792/a6ba112/parent_file

This helped me find the text between two parentheses - regardless of the length of the string between them! Sorry I repeated this because I wanted to make sure I could say this in as many ways as I could so people can find this when they google it!

Seriously, thank you so much!