Extracting Month and Year from File Name

[bearcub]

I have the following formula in A1 to show the current file Name

MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Result in A1:
Aug 2018 New Employee-Term File.xlsx

[code//] <- What is the correct syntax so it shows up as code (I forgot how to do it)

I would like to extract the month and year portion of the file name.

I did try using the left combined with the Search and Find functions but was unsuccessful.

Thank you for your help,

Michael

 

[bearcub]

Thank you, So there isn't a way of making that last argument expand or contract depending upon the character length - like June, January, May would be different lengths. Depending upon the format, I would have to adjust the string length depending upon the month.

Is there a way to do this automatically?

 

[JoeMo]

Thank you, So there isn't a way of making that last argument expand or contract depending upon the character length - like June, January, May would be different lengths. Depending upon the format, I would have to adjust the string length depending upon the month.

Is there a way to do this automatically?

Using September as the month with the longest character length (9) and adding 5 (space + yyyy) to it, this should do what you have requested:

=MID(MID(CELL("filename"),FIND("[",CELL("filename"))+1,14),1,SEARCH(" ",MID(CELL("filename"),FIND("[",CELL("filename"))+1,14))+4)

 

[bearcub]

My apologies. I just created a 3 dummy files with different month name lengths and got what I wanted. Really great formula, how does it work? I was under the impression that I would have to change 14 to 15 to 13, etc.

What makes it behave the way it does. I understand the formula but I don't understand how it is dynamic.

Thank you again for your help

 

[JoeMo]

My apologies. I just created a 3 dummy files with different month name lengths and got what I wanted. Really great formula, how does it work? I was under the impression that I would have to change 14 to 15 to 13, etc.

What makes it behave the way it does. I understand the formula but I don't understand how it is dynamic.

Thank you again for your help

You are welcome.

It's dynamic because it assumes worst-case month name character length which would be September having 9 characters then adds 5 more characters for the year (assumed to always be in the format yyyy) and a space between the month and the year, then using the mid function on those first 14 characters, finds the first space (i.e. the space between month and year) and adds 4 for the year.

 

[bearcub]

Ah, I see. If I had a name that exceed 14 characters (like 20, for example), it would work? Anything less than 20 characters would show up correctly, correct? The Search function it appears is what makes this magic happen, correct?

 

[JoeMo]

Ah, I see. If I had a name that exceed 14 characters (like 20, for example), it would work? Anything less than 20 characters would show up correctly, correct? The Search function it appears is what makes this magic happen, correct?

Yes,yes and yes.

 

[bearcub]

Really cool formula, thank you again

 

[JoeMo]

Really cool formula, thank you again

You are welcome - thanks for the reply.

 

[bearcub]

Thank you for taking the time out of your day to answer my question - I really appreciate it!

 

[Peter_SSs]

.. or this?

=TRIM(LEFT(SUBSTITUTE(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),"")," ",REPT(" ",20)),40))

Not sure if it is possible with your files, but as well as being a bit shorter this would also work if the year was abbreviated to 2 digits (eg "September 18 New Employee-Term File.xls")