SOLVED

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

New Contributor

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 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Kenneth Green 

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

Maybe with this formula.

text string.JPG 

@Quadruple_Pawn 

 

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.