CODEWITHSUNDEEP
excelvbaexcel-formula
Parseltongue
Parseltongue

Reputation: 11657

Excel: Extract Numbers from Date Strings

I have several malformed date columns. I am trying to transform these columns into "month", "year", and "start_day" columns.

Here is an example sample:

January 13th, 2018
January 13th-14th 2018
January 5th-7th 2018
January 4th-8th 2018
December 9th-10th 2017
December 2nd-3rd 2017
December 2nd, 2017
December 2nd-3rd 2017
November 18th, 2017
November 18th-20th, 2017
November 17th-19th 2017
November 11th, 2017
November 11th-12th, 2017
November 11th-12th 2017

Note that sometimes there is a comma between the day abbreviation and the year, other times not. My desired output (for the day column) would be:

13
13
5
4
9
2
2
2
18
18
17
11
11
11

I am not concerned about the second date (to the right of the hyphen). I was able to get the month with LEFT(A1, 3) and the year with RIGHT(A1, 4). I cannot for the life of me figure out how to grab the first numerical value to the right of the month without resorting to regex. Any ideas?

Upvotes: 2

Views: 1010

Answers (5)

Scott Craner
Scott Craner

Reputation: 152450

Another alternate:

=AGGREGATE(14,6,--MID(A1,FIND(" ",A1)+1,{1,2}),1)

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Alternate solution:

=LOOKUP(99,--MID(A1,FIND(" ",A1)+1,{1,2}))

Upvotes: 2

Greg N.
Greg N.

Reputation: 3

You can use a formula that could take care of this;

=DATE(VALUE(RIGHT(A1,4)),VLOOKUP(MID(A1,1,3),$G$1:$H$12,2),MID(A1,FIND(" ",A1)+1,(FIND("t",A1)-FIND(" ",A1)-1)))

This also assumes that there is a lookup table that has JAN, FEB, etc... with the number of the month. That table needs to be sorted alphabetically by the month. It should look like this; APR 4 AUG 8 DEC 12 FEB 2 JAN 1 JUL 7 JUN 6 MAR 3 MAY 5 NOV 11 OCT 10 SEP 9

Also, you will have to do a search and replace the 'nd' with 'th' to avoid having to add a bunch of extra conditional code here.

I would also suggest that a second column of dates be added that would be populated by the 'to' dates that may appear.

Hope that helps.

Greg

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

A VBA solution might be:

Sub test()
    Dim s() As String
    Dim i As Long
    For i = 1 To 14
        'Split text based on spaces
        s = Split(Cells(i, "A").Text)
        'Join bits together and convert to date
        'Val(Left(s(1), 2)) gives the day portion
        'Month and Year are directly from the source
        Cells(i, "B").Value = CDate(Val(Left(s(1), 2)) & " " & s(0) & " " & s(2))
    Next
End Sub

(Assumes data in A1:A14, and puts results in B1:B14)

Upvotes: 2

Greg Viers
Greg Viers

Reputation: 3523

=IF(ISNUMBER(1*MID(A1,FIND(" ",A1)+2,1)),MID(A1,FIND(" ",A1)+1,2),MID(A1,FIND(" ",A1)+1,1))*1

Upvotes: 6

Related Questions