Extract Month from file name

[ruckuz]

Hi,
I need to extract the month of the year from the file name.

\\srv-ef-w-001\shared\Financial Statements\Financial Statements\Month End 2016\12 December 2016\[Lease Disclosure - December.xlsx]Voucher Summary

The file path above I used the cell filename function to get the full address of the file. However, I just need December and I tried the mid formula but since July has less letters than december, I would get blank spaces for july. my question is how do i get july and december w/o the extra spaces after or (r)? I just want July or December w/o any empty spaces afterwards. Thanks!

 

[Joe4]

Assuming that the file name always begins with: "[Lease Disclosure - "
try:

=TRIM(MID(A1,FIND("[Lease Disclosure - ",A1)+20,FIND(".xlsx",A1)-FIND("[Lease Disclosure - ",A1)-20))

 

[AlKey]

Or try this

=TEXT(TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("\[",A1),LEN(A1),""),"",REPT(" ",255)),255)),"mmmm")

 

[ruckuz]

Thank you! works perfectly!

What does the [ before lease disclosure do? i'm not quite following the formula after FIND.

 

[Joe4]

I am using the MID formula (see: https://www.techonthenet.com/excel/formulas/mid.php).
So, we need to find the starting position, which is just AFTER the string "[Lease Disclosure - ". So we need to go 20 spaces after the beginning of that to find where the beginning of our month is.
In the last argument, we need to find the length. So, we find where ".xlsx" starts, and subtract out where the first part starts.

 

[ruckuz]

Ah, i get it now. i understand the MID formula, I just didn't get the "[lease..." thank you so much! I will save this post for future references.

 

[Joe4]

I was just looking for something identifiable to locate just before the month name to hone in on.