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.