Excel VBA 从字符串中获取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12963033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA get date from a string
提问by capm
If I have a cell with the following:
如果我有一个具有以下内容的单元格：
Tuesday, April 16th 2009
How do I convert that string into a date format recognized by Excel. I think I have to use MID() and FIND() functions.
如何将该字符串转换为 Excel 识别的日期格式。我想我必须使用 MID() 和 FIND() 函数。
回答by ccampj
Here is a function that would work from VBA and as a User Defined Function
这是一个可以从 VBA 工作并作为用户定义函数的函数
Function GetDate(InString As Range) As Date
Dim newDate As Date
Dim tmpDate As String
'Sample Date
'Tuesday, April 16th 2009
'Remove the Day of the Week.
tmpDate = Trim(Mid(InString, InStr(InString, ",") + 1))
'Get rid of "th"
tmpDate = Replace(tmpDate, "th ", " ")
'Get rid of "rd"
tmpDate = Replace(tmpDate, "rd ", " ")
'Get rid of "nd"
tmpDate = Replace(tmpDate, "nd ", " ")
'Get rid of "st"
tmpDate = Replace(tmpDate, "st ", " ")
'Convert string to date
newDate = DateValue(tmpDate)
GetDate = newDate
End Function
回答by SeanC
Assuming that text is in A1, we can break it out into separate parts, and use DATEVALUEto put it together.
假设文本在 A1 中，我们可以将其拆分为单独的部分，然后使用DATEVALUE将其组合在一起。
B1: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))
B2: =IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))
B3: =RIGHT(A1,4)
B4: =DATEVALUE(B2&" "&B1&" "&B3)
Or, you can do it in one go:
或者，您可以一次性完成：
=DATEVALUE(IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))&" "&RIGHT(A1,4))
