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
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.
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.
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
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!
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.
Seriously, thank you so much!
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.
The code above works with different characters ie < and >, ( and ). I wanted to use |bars| in my code/scoring system, so worked on this that my supervisor finally got working:
MID( {Text}, FIND(’|’,{Text})+1, (FIND(’|’,{Text},(FIND(’|’,{Text})+1)) - (FIND(’|’,{Text}))-1))
For some reason the one that works on two characters returned a null/blank value for the | bar| version.
This was super helpful! I have a somewhat related question, I’m hoping I could get some help on.
I’m trying to extract the value in a string query, where sometimes it’ll be the only value, and sometimes there will be others.
E.g.,
getpresently.com/hello?event=123
getpresently.com/hello?event=123&utm=newsletter&test=hi
getpresently.com/goodbye?event=12345&utm=newsletter&test=hi
I’m curious if there’s an easy way to do this. There are a few variable digits which make it hard to do a simple left/right:
Is there any way to pull out just what’s after the “event=” but skip anything that may follow an &?
So I am trying to leverage this against the following string:
New voicemail in mailbox 1 from “FIRSTNAME LASTNAME” <123456789>
Using:
MID(
Subject,
FIND(
‘"’,
Subject
)+1,
(FIND(
‘"’,
Subject
)
)-(FIND(
‘"’,
Subject
)+1)
)
But all it returns is “-1”. What have I missed? Any help is appreciated!
This was super helpful! I have a somewhat related question, I’m hoping I could get some help on.
I’m trying to extract the value in a string query, where sometimes it’ll be the only value, and sometimes there will be others.
E.g.,
getpresently.com/hello?event=123
getpresently.com/hello?event=123&utm=newsletter&test=hi
getpresently.com/goodbye?event=12345&utm=newsletter&test=hi
I’m curious if there’s an easy way to do this. There are a few variable digits which make it hard to do a simple left/right:
Is there any way to pull out just what’s after the “event=” but skip anything that may follow an &?
left(
substitute(left(Text,6+find(‘event=’,Text)),‘’),
find(‘&’,Text)-find(‘event=’,Text)-6
)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.