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,

 

[JoeMo]

I'd be inclined to use a UDF like the one below although I suspect it can be done with native excel functions.
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function. See example below the UDF code.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function ExtractYYYY_MM(S As String)
Dim Sp As Variant, i As Long
If InStr(S, "\") = 0 Then
    ExtractYYYY_MM = CVErr(xlErrNA)
    Exit Function
End If
Sp = Split(S, "\")
For i = LBound(Sp) To UBound(Sp)
    If Sp(i) Like "#### ##" And IsDate(Sp(i)) Then
        ExtractYYYY_MM = CDate(Sp(i))
        Exit Function
    Else
        ExtractYYYY_MM = ""
    End If
Next i
End Function

Sheet1

	A	B
1	c:\abc\2017 10\[abc.xlsx]	2017 10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:163px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =ExtractYYYY_MM(A1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[TorontoNewf]

It will be used for many files (.xlsx), and
I find that macros usually confuse others.

Is there any way I can do this with just Excel functions?

 

[JoeMo]

It will be used for many files (.xlsx), and
I find that macros usually confuse others.

Is there any way I can do this with just Excel functions?

The "macro" need not be visible to users. Just inform them there is a new function that can be used on their worksheets called ExtractYYYY_MM. When they begin to type "=Extrac" there will be a tool tip suggesting the use of that function. Pressing tab will enter "=ExtractYYYY_MM(" in the cell and the user can then click on the cell from which to extract the date and close the parentheses. Try it yourself.

BTW, the cells holding this formula should be formatted as a date "YYYY MM" or the result will be the Excel serial date for the first day of the month in the specific year.

 

[TorontoNewf]

I agree re: macros argument;
my Personal.xlsb has LOTS of them!

My point is that I will need to put in all kinds of files in our (already complex) model,
including .xlsx, and relying on others is, at best, tricky.

Hence the search for non-macro solutions.

 

[JoeMo]

Understood. I'll reply here to bump this back to the top because, as I said in post #2 , I suspect the extraction can be done with native Excel functions.

 

[TorontoNewf]

Bump.

 

[Sunny Kow]

Hi
If you could show as many types of path as possible then it would really help.
For example:
D:\12345\TESTING\2007 12\TEST\[123.XLSM]
C:\345\MY FOLDER\2016 12\REPORTING\[123.XLSM]
F:\PUBLIC\FOLDER\2018 12\TESTING\[123.XLSM]
This will allow us to identify any patterns than can be used to extract what you wanted.

Assuming that you only have years such as 20xx (like above), then you could try this
=MID(A1,SEARCH("20",A1),7)
Hope this helps.

 

[TorontoNewf]

Unfortunately, there is no real pattern.
The date will be just before the file name approx 90% of the time,
but it is possible the file has been buried one/two layers down from the date folder.

In the 90%, '=MID(B$3, FIND("\[", B$3)-7, 7)' works just fine.
In the other scenarios, it does not.

I need to extract it regardless of its location within the string/path,
but with no luck so far.
Your input will be VERY appreciated!

 

[Sunny Kow]

Without enough examples of the paths that you have, it is difficult to give you a more accurate answer.
Please provide more examples (between 10 to 15).
You can also try =MID(A1,SEARCH("\???? ??",A1)+1,7)