With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

I am trying to parse off of this text string... first everything right of the second "_" and everything left of the file format "." How would I write a formula that would result in "EA-010" from the following:

Abaca_Brass_EA-010.jpg needs to = EA-010

Please advise and thanks. -R-

Abaca_Brass_EA-010.jpg needs to = EA-010

Please advise and thanks. -R-

=MID(A1,FIND("_",A1,FIND("

Put this in A1

Abaca_Brass_EA-010.jpg

and put this in B1

=MID(A1,FIND("_",A1,FIND("

gowflow

MID has a structure of MID(Text,Start Position,Number of Character)

this will extract from the Text or a cell reference at the starting position a certain number of characters.

FIND has the structure FIND(Find Text,Within Text,optional start number)

This will find the position of a certain character within string or a cell reference.

What we do is a double find to get the position of the second _ and increment to 1 this will give us the position of the first character after the second _

FIND("_",A1,FIND("_",A1)+1

then Find(A1,".") will give us the position of the .

then we construct with MID

MID(A1 or the whole text,at position the double find like above,the length is position of the find minus position of the double find)

you get your text !!

Hope above explains it.

gowflow

In a mid statement you would normally have:

Mid(

So for the

Find the first "_" and use that number(+1) as a start number to look for the second"_" then add 1 so you are starting your MID after the second "_",

Number of characters is:

Find the "." and

subtract the start number you used to start after the second "_".

This way you are starting after the second "_" and ending just before the ".".

=MID(A1,FIND("_",A1,FIND("

so this first part finds the second "_" in A1. and adds a char to remove the "_" and marks the start point.

FIND(".",A1)-(FIND("_",A1,

and this second part finds the dot, and subtracts it from the start point?

It looked odd that it is finding the start point twice.. I think I am getting this..

So to grab everything left of the second _ (including it)

=LEFT(A1, FIND("_",A1)) ?? -R-

so this first part finds the second "_" in A1. and adds a char to remove the "_" and marks the start point.

is this:

FIND(".",A1)-(FIND("_",A1,

This

FIND(".",A1)-(FIND("_",A1,

give you the Length of the text to parse in the formula !!!

You cannot use this formula to get your second part look at my comment above !

gowflow

to a "+"? or is the hyphen mean from here to here no matter the direction?

It is making sense... sorry for all the quesitons.. (for understanding). -R-

gowflow

It counts how far in the second "_" is and starts at the next character.

Then it finds the "." and subtracts the first number from it to find the length of what's left.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

gowflow

parseformula.xls