Excel Search Date formats

Question

I am using Excels 2013 mid and search function to get the date from a comment column but I don't know how to do this to cover the 8 date formats(dd/mm/yyyy, dd/mm/yy and so on). Three of my formulas are below although I would need to join them all together to capture all the date formats.The comment column can be any text with a date anywhere within it.

=MID(A6, SEARCH("??/??/????", A6, 1), 10)
=MID(A7, SEARCH("?/??/????", A7, 1), 9)
=MID(A8, SEARCH("??/?/????", A8, 1), 9)

There must be a better way of doing this as the only way I can think of is to put each formula on a separate column and then use an if statement to show the date. Any help would be much appreciative. thanks

Sample Column data

Paid on 01/01/2017
Paid on 1/01/2017, Reference AAA123
Reference BBB456 Paid on  01/1/2017

Can you give a sample of what your A column looks like? – Alexis Olson Nov 14 '17 at 18:40 — Alexis Olson, Nov 14 '17 at 18:40
Can there be other numbers in the string? – Scott Craner Nov 14 '17 at 18:47 — Scott Craner, Nov 14 '17 at 18:47

Scott Craner · Accepted Answer · 2017-11-15T13:43:15.123

5

This array formula works if there are no other numbers before the date in the string:

=INDEX(--TRIM(CLEAN(MID(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",999)),",",""),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999))),MATCH(TRUE,ISNUMBER(--TRIM(CLEAN(MID(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",999)),",",""),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)))),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

It parses the string by the space and iterates through each "word" till it finds the first that can be converted to a number, which a date is.

This will require that the output be formatted into the date format desired.

edited Nov 15 '17 at 13:43

answered Nov 14 '17 at 18:59

Scott Craner

148,073
10
49
81

Just checked and this formula doesn't work if the string ends with a date, at least not in Excel 2010. Still, enough for a +1. – ImaginaryHuman072889 Nov 14 '17 at 19:15
@ImaginaryHuman072889 my bad forgot the pesky `+1` The edit should work now. – Scott Craner Nov 14 '17 at 19:20
Thank you for this, it almost works although it doesn't like the full stops and commas, is it possible to add a substitute on a comma/Full stop to this so that it covers my second example shown on my updated sample column? – New2Programming Nov 15 '17 at 10:10
1

Thanks @ScottCraner This worked really well, also learned a few extra functions that I was unaware of. – New2Programming Nov 15 '17 at 13:55

n8. · Answer 2 · 2017-11-14T20:31:00.120

This is what Regular Expressions are used for.

Here's a function that implements Regular Expressions as referenced here: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

(Note that you have to add the "Microsoft VBScript Regular Expressions 5.5" reference, as mentioned in the link)

Function showDate(thisVal As String)

    Dim strPattern As String
    Dim regEx As New RegExp

    strPattern = "[0-9]?[0-9]/[0-9]?[0-9]/[0-9]?[0-9]?[0-9][0-9]"

    If strPattern <> "" Then

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        Set objMatches = regEx.Execute(thisVal)
        For Each Match In objMatches             'The variable match will contain the full match
            showDate = Match
        Next

    End If

End Function

Use it like: =showDate(A1)

the bit of code:

    strPattern = "[0-9]?[0-9]/[0-9]?[0-9]/[0-9]?[0-9]?[0-9][0-9]"

Controls what patterns will be looked for. The ? after each [0-9] range denotes an "optional" character, so the leading zero or one in the month can be accepted if present, but the lack of it won't cause the match/find to miss.

Do note that this solution provides the last instance of a date from a string. If you only have one instance of a date it's fine but multiple dates per string is a consideration you have to scope for.

Excel Search Date formats

2 Answers2