Extract a date out of a workbook path

[TorontoNewf]

Greetings,

I have a path that includes a date in a "YYYY MM" format
i.e. c:\abc\2017 10\[abc.xlsx]

Extract it is easy if it is always just before the file name,
but not if it is elsewhere in the path.
(at least not for me!)


Any suggestions?

Thanks,

 

[Saxus]

I agree. The problem into what TorontoNewf paths differ in file title and path also. so there's no definite number of characters from either left or right of the said date value that he wants to grab.....

 

[Sunny Kow]

Agreed, there may not be a fixed pattern. Since the OP managed to resolved 90% of them, then we only need to look at the other 10%.
Until now we have no idea what the other 10% looks like since the OP did not give enough examples.

We may require more than one formula/solution to get the work done.

 

[TorontoNewf]

The examples you provided were good examples.
The most likely possibilities (and easiest):
C:\345\2016 12\[123.XLSM]
C:\345\MY FOLDER\REPORTING\2016 12\[123.XLSM]

However, the following are also possible:
C:\345\2016 12\REPORTING\[123.XLSM]
C:\345\2016 12\MY FOLDER\REPORTING\[123.XLSM]
C:\2016 12\345\MY FOLDER\REPORTING\[123.XLSM]

Essentially, the '\2016 12\' can be buried anywhere in the path.

 

[Sunny Kow]

Hi
If this is all the combination you have then you can try this. The date can be buried anywhere in the path. It doesn't really matter.
=SUBSTITUTE(MID(A1,SEARCH("20",A1),7),"","")
You can replace the 20 with 19 if you have years such as 19xx
The reason in replacing the \ is just in case you have dates such as 2016 1 or 2016 2 etc as I don't know how 1-digit month is shown in your path (2016 1 or 2016 01)
Hope this helps.

 

[TorontoNewf]

Thanks Sunny;
you are right - I did not include enough examples/possibilities in my post.
Nonetheless, I used your formula to tweak my original formula to make it work.

For those interested, my original target string was based on "=CELL("filename", A3)", which would produce the following
[TABLE="width: 888"]
<tbody>[TR]
[TD="class: xl49476, width: 888"]Q:\Corporate Financial\Planning and Performance Measurement\05 Forecasting\Forecast Model\2018 01\[Expenses, CFF.xlsx]Misc
"=MID(B$3, FIND("\[", B$3)-7, 7)" will extract the date.

This does not work if the "\2018 01\" is anywhere else in the string.
Also, I did not include (in the original post) a potential "\2018\" within this string [my bad!].

My final solution:
New string: "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CELL("filename", A3), "\2018", ""), "\2019", ""), "\2020", "")"
Date Extract: "=MID(B$3, SEARCH("20", B$3), 7)"
[/TD]
[/TR]
</tbody>[/TABLE]

The new string formula is not the most eloquent formula I've written, but the sheet layout limits my options.
It is likely adequate for my purposes (and in fact, I included enough substitutions for 6 years just to be sure!)

Again, thanks for your input, Sunny!