Excel Forumla to Extract data between first and last occurrance of specific charactersin a cell?

[Kenneth Green]

Excel Forumla to Extract data between first and last occurrance of specific character

I need an Excel Forumla that will extract and return the data between first and last occurrance of the _ (underscore) character.

I have around 400+ files I have been given to rename from the format they were provided in to one that we work in and I am trying to write a formula that will produce the rename code (string) I can copy in to a CMD window to do this.

I have written formulas that take the contents of a specified cell and adds the CMD shell rename code, ie: 

ren "old flle name" "new file name"

which works perfectly.

I have no problem getting a list of file names in to a single column in a worksheet and this is the format of the naming scheme for the files I have been given (these are dummy names of course).

01_Green_Yellow_Blue_Red_Purple_Black.xls
100_Orange_Apple_Plum_Pineapple_Yellow.xls

I need
Green_Yellow_Blue_Red_Purple
Orange_Apple_Plum_Pineapple

So far I have got..

=MID(A1,3+1,LEN(A1)-(8+10))

This works as expected as long as there are only two characters before the first _ (underscore), however if there are three characters as seen in line two of my first example, (100_Orange_Apple_Plum_Pineapple_Black.xls) it returns..

_Orange_Apple_Plum_Pineapple

Can anyone kindly point me in the right direction?

[#2 · Accepted solution]

@Kenneth Green 

=MID(A1,FIND("_",A1)+1,FIND("|",SUBSTITUTE(A1,"_","|",(LEN(A1)-LEN(SUBSTITUTE(A1,"_","")))/LEN("_")))-1-FIND("_",A1))

Maybe with this formula.

 

[#3]

@OliverScheurich 

Thank you for kindly helping.

It works perfectly and I would never have been able to work out something that complex.

You have really helped me.