Data John exp 052021_46857.pdf
|
Data John exp 052021_46857.pdf
|
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) |
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
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
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?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
Power Query: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
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