Extracting date from string of text

[ESACAWIP]

Data John exp 052021_46857.pdf Tom_46854.pdf Jerry 5-31-2021_35585.pdf Ford_Exp 05.31.2020_24707.pdf David exp09302016_14115.pdf Mike 2.28.2019_12495.pdf Result: John 05.20.21 Tom Jerry 05.31.2021 Ford 05.31.2020 David 09.30.2016 Mike 02.28.2019 Hi, I am having some trouble extracting dates from these strings of text. In the raw data, I am given a mixture of dates written in various formats. Worse, there are numbers that I need to exclude that are non date values such as "46857" in the line "John exp 052021_46857.pdf". I appreciate if anyone can help!

 

[JGordon11]

This might do about half of what you want. I decided to stop at this point, I may pick it back up later and add the date clean up code. It relies on the filename having 20xy somewhere in the name (x and y being digits 0-9). So if you've got dates in other centuries or people are just using the tens and ones places for the year this will be even less helpful.

Option Explicit

Function ExtractNameAndDate(t As String) As String

    Dim FirstName As String, ti As String, k As Integer, yr As Integer, i As Integer
    Dim dtchr As String, dtstr As String
    
    k = 1
    ti = Mid(t, 1, 1)
    Do While UCase(ti) >= "A" And UCase(ti) <= "Z"
        FirstName = FirstName & ti
        k = k + 1
        ti = Mid(t, k, 1)
    Loop
    
    yr = InStr(1, t, "20")
    
    If yr = 0 Or yr > Len(t) - 2 Then
        ExtractNameAndDate = FirstName
        Exit Function
    End If
    
    If Not IsNumeric(Mid(t, yr + 2, 2)) Then
        ExtractNameAndDate = FirstName
        Exit Function
    End If
    
    i = yr - 1
    dtchr = Mid(t, i, 1)
    Do While IsNumeric(dtchr) Or dtchr = "." Or dtchr = "-" Or dtchr = "/"
        i = i - 1
        If i = 0 Then
            ExtractNameAndDate = FirstName
            Exit Function
        End If
        dtchr = Mid(t, i, 1)
    Loop
    i = i + 1
    dtstr = Mid(t, i, yr - i + 4)
    
    ExtractNameAndDate = FirstName & " " & dtstr
    
End Function

MrE20210127.xlsm
	A	B
1	John exp 052021_46857.pdf	John 052021
2	Tom_46854.pdf	Tom
3	Jerry 5-31-2021_35585.pdf	Jerry 5-31-2021
4	Ford_Exp 05.31.2020_24707.pdf	Ford 05.31.2020
5	David exp09302016_14115.pdf	David 09302016
6	Mike 2.28.2019_12495.pdf	Mike 2.28.2019
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=ExtractNameAndDate(A1)

 

[Zot]

Using Regular Expression

Function ExtractNameDate(cell As Range) As String

Dim Element As Variant
Dim Name$, DT$, Pattern$, ArryPattern$()
Dim StrCapture As Object

Set RegEx = CreateObject("VBScript.RegExp")

Pattern = "\d{6},\d{8},\d?\d[(\-)|(\.)]\d{2}[(\-)|(\.)]\d{4}"
ArryPattern = Split(Pattern, ",")

With RegEx
    .Global = True
    .Pattern = "^[a-zA-Z]+"
End With
Name = RegEx.Execute(cell)(0)

For Each Element In ArryPattern
    With RegEx
        .Global = True
        .Pattern = Element
    End With
    If RegEx.Test(cell) Then
        DT = RegEx.Execute(cell)(0)
    End If
Next
ExtractNameDate = Name & " " & DT

End Function

 

[sandy666]

Data		Name.1	Date
John exp 052021_46857.pdf		John	052021
Tom_46854.pdf		Tom
Jerry 5-31-2021_35585.pdf		Jerry	5-31-2021
Ford_Exp 05.31.2020_24707.pdf		Ford	05.31.2020
David exp09302016_14115.pdf		David	09302016
Mike 2.28.2019_12495.pdf		Mike	2.28.2019

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LC = Table.TransformColumns(Source,{{"Data", Text.Lower, type text}}),
    TBD = Table.AddColumn(LC, "Name", each Text.BeforeDelimiter([Data], "_", {0, RelativePosition.FromEnd}), type text),
    SplitAny = Table.SplitColumn(TBD, "Name", Splitter.SplitTextByAnyDelimiter({" ","exp","_"}, QuoteStyle.Csv)),
    TCC = Table.CombineColumns(SplitAny,{"Name.2", "Name.3", "Name.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date"),
    Proper = Table.TransformColumns(TCC,{{"Name.1", Text.Proper, type text}}),
    RC = Table.RemoveColumns(Proper,{"Data"})
in
    RC

 

[sandy666]

or a bit modified

Data		Data.1	Date
John exp 052021_46857.pdf		John	01/05/2021
Tom_46854.pdf		Tom
Jerry 5-31-2021_35585.pdf		Jerry	31/05/2021
Ford_Exp 05.31.2020_24707.pdf		Ford	31/05/2020
David exp09302016_14115.pdf		David	30/09/2016
Mike 2.28.2019_12495.pdf		Mike	28/02/2019

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LC = Table.TransformColumns(Source,{{"Data", Text.Lower, type text}}),
    TBD = Table.TransformColumns(LC, {{"Data", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    SplitAny = Table.SplitColumn(TBD, "Data", Splitter.SplitTextByAnyDelimiter({" ","_","exp"}, QuoteStyle.Csv)),
    Proper = Table.TransformColumns(SplitAny,{{"Data.1", Text.Proper, type text}}),
    TCC = Table.CombineColumns(Proper,{"Data.2", "Data.3", "Data.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Mrg"),
    IF = Table.AddColumn(TCC, "Date", each if [Mrg] = "" then null else if not Text.StartsWith([Mrg], "0") then "0" & [Mrg] else if Text.Length([Mrg]) < 8 then Text.Insert([Mrg], 2, "/01/") else if Text.Length([Mrg]) = 8 then Text.Insert(Text.Insert([Mrg],4 , "/"), 2, "/") else [Mrg] ),
    DateUS = Table.TransformColumnTypes(IF, {{"Date", type date}}, "en-US"),
    RC = Table.RemoveColumns(DateUS,{"Mrg"}) 

in
    RC

 

[JGordon11]

or a bit modified

Data		Data.1	Date
John exp 052021_46857.pdf		John	01/05/2021
Tom_46854.pdf		Tom
Jerry 5-31-2021_35585.pdf		Jerry	31/05/2021
Ford_Exp 05.31.2020_24707.pdf		Ford	31/05/2020
David exp09302016_14115.pdf		David	30/09/2016
Mike 2.28.2019_12495.pdf		Mike	28/02/2019

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LC = Table.TransformColumns(Source,{{"Data", Text.Lower, type text}}),
    TBD = Table.TransformColumns(LC, {{"Data", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    SplitAny = Table.SplitColumn(TBD, "Data", Splitter.SplitTextByAnyDelimiter({" ","_","exp"}, QuoteStyle.Csv)),
    Proper = Table.TransformColumns(SplitAny,{{"Data.1", Text.Proper, type text}}),
    TCC = Table.CombineColumns(Proper,{"Data.2", "Data.3", "Data.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Mrg"),
    IF = Table.AddColumn(TCC, "Date", each if [Mrg] = "" then null else if not Text.StartsWith([Mrg], "0") then "0" & [Mrg] else if Text.Length([Mrg]) < 8 then Text.Insert([Mrg], 2, "/01/") else if Text.Length([Mrg]) = 8 then Text.Insert(Text.Insert([Mrg],4 , "/"), 2, "/") else [Mrg] ),
    DateUS = Table.TransformColumnTypes(IF, {{"Date", type date}}, "en-US"),
    RC = Table.RemoveColumns(DateUS,{"Mrg"})

in
    RC

Query doesn't return a date if the month has two digits with the first being a 1 instead of 0. For example if David's file name is changed to "David exp12302016_14115.pdf". Can it be modified to account for Oct-Dec months?

 

[sandy666]

it works with given example (post#1) which should be representative

Data		Data.1	Date
John exp 052021_46857.pdf		John	01/05/2021
Tom_46854.pdf		Tom
Jerry 5-31-2021_35585.pdf		Jerry	31/05/2021
Ford_Exp 05.31.2020_24707.pdf		Ford	31/05/2020
David exp09302016_14115.pdf		David	30/09/2016
Mike 12.28.2019_12495.pdf		Mike	28/12/2019
Mike 2.28.2019_12495.pdf		Mike	28/02/2019
Joe exp 62021_46857.pdf		Joe	01/06/2021
exp 82021_46857.pdf			01/08/2021

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LC = Table.TransformColumns(Source,{{"Data", Text.Lower, type text}}),
    TBD = Table.TransformColumns(LC, {{"Data", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    SplitAny = Table.SplitColumn(TBD, "Data", Splitter.SplitTextByAnyDelimiter({" ","_","exp"}, QuoteStyle.Csv)),
    Proper = Table.TransformColumns(SplitAny,{{"Data.1", Text.Proper, type text}}),
    TCC = Table.CombineColumns(Proper,{"Data.2", "Data.3", "Data.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Mrg"),
    TS = Table.AddColumn(TCC, "TS", each if [Mrg] = "" then null else if Text.Length([Mrg]) < 6 then "0" & [Mrg] else Text.Select([Mrg], {"0".."9"})),
    IF = Table.AddColumn(TS, "IF", each if [Mrg] = "" then null else if Text.Length([TS]) <> 8 and not Text.StartsWith([TS], "0") then "0" & [TS] else if Text.Length([TS]) <= 6 then Text.Insert([TS], 2, "/01/") else [TS]),
    Date = Table.AddColumn(IF, "Date", each if [IF] = null then null else if Text.Length([IF]) > 8 then [IF] else Text.Insert(Text.Insert([IF],4 , "/"), 2, "/")),
    DateUS = Table.TransformColumnTypes(Date, {{"Date", type date}}, "en-US"),
    TSC = Table.SelectColumns(DateUS,{"Data.1", "Date"})
in
    TSC