Excel:从日期字符串中提取数字 [英] Excel: Extract Numbers from Date Strings

问题描述

我有几个格式错误的日期列.我正在尝试将这些列转换为月"，年"和"start_day"列.

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

以下是示例示例:

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

我不关心第二个日期(连字符的右边).我可以使用 LEFT(A1，3)获得月份，并通过 RIGHT(A1，4)获得年份.我一生都无法弄清楚如何在不借助正则表达式的情况下获取月份右侧的第一个数值.有什么想法吗?

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?

推荐答案

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

这篇关于Excel:从日期字符串中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！