Excel-VBA：从字符串查找和替换以及日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19772933/
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: Find and Replace and Date Formatting from String
提问by SIEDASC
' Try to format the dates
Range("N:N").Select
Selection.NumberFormat = "dd/MM/yyyy"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Using this code to try and fix a problem with some downloaded data I don't control. The Table has a Space in front of a date stored as text. e.g. " 04/11/2013"
使用此代码尝试解决一些我无法控制的下载数据的问题。表格在存储为文本的日期前有一个空格。例如“04/11/2013”
Doing a Manual Find and Replace in Excel fixes the Issue but I want Pivot the data and group afterwards and when I try and do it with VBA it does two things...
在 Excel 中进行手动查找和替换修复了该问题，但我希望之后对数据和组进行透视，当我尝试使用 VBA 执行此操作时，它会做两件事...
It doesn't recognise all records as dates. Some remain as General even though the Cell format is changed. This means the user having to go through each line with F2+Enter, then a lot of messing around with the pivot table.
Its Reversing the Day/Month. i.e. the Original Data is 01/10/2013 (Oct 1st) and it converts it into Janauary 10th.
它不会将所有记录识别为日期。即使单元格格式已更改，有些仍为常规。这意味着用户必须使用 F2+Enter 浏览每一行，然后大量处理数据透视表。
它逆转了日/月。即原始数据是 2013 年 1 月 10 日（10 月 1 日），并将其转换为 1 月 10 日。
Is there a fix for the Find/Replace or a Way of looping through to fix the Cell Formatting.
是否有针对“查找/替换”的修复方法或通过循环修复单元格格式的方法。
采纳答案by Sam
For a none VBA solution you could try the DATEVALUEfunction in this instance.
对于非 VBA 解决方案，您可以DATEVALUE在此实例中尝试该功能。
Usage would be something like
用法类似于
=DATEVALUE(Trim(A1))
or
或者
=DATEVALUE(RIGHT(A1, LEN(A1)-1)
assuming your date is in cell A1
假设您的日期在单元格 A1 中
And for a VBA solution, something like
对于 VBA 解决方案，类似于
Public Sub ConvertToDate()
Dim endRow As Long
Dim ws As Worksheet
Dim dateColumn As Long
Dim dateval As String
Set ws = Sheet1
'set date column (in this case we set column A)
dateColumn = 1
endRow = ws.Cells(ws.Rows.Count, dateColumn).End(xlUp).Row
For i = 2 To endRow
Dim length As Long
length = Len(ws.Cells(i, dateColumn)) - 1
'just a quick and dirty check to see if there is value data
'it isn't set to check for numeric data, so if there is some dodgy
'string data in the cell then it will fail on the CDate line.
If length > 3 Then
'store date string (may use Trim() or Mid() etc...)
dateval = Right(ws.Cells(i, dateColumn).Value, length)
'convert to date and change cell value
ws.Cells(i, dateColumn).Value = CDate(dateval)
End If
Next i
End Sub
回答by nutsch
Make sure your Windows regional settings are set for dd/mm/yy.
确保您的 Windows 区域设置为 dd/mm/yy。
Do your find/replace
做你的查找/替换
If you need additional processing, run Text to columns on the date column.
如果您需要额外的处理，请在日期列上运行 Text to columns。
回答by Stu Guerin
I have spent around two hours searching all over google for an answer to this, and nothing was working for me, even though I have my Windows regional settings set for dd/mm/yy. The only VBA function which I've gotten to convert a date into a forward slash dd/mm/yyyy format, is the TextToColumns function.
我花了大约两个小时在谷歌上搜索这个问题的答案，但没有任何东西对我有用，即使我为 dd/mm/yy 设置了我的 Windows 区域设置。我将日期转换为正斜杠 dd/mm/yyyy 格式的唯一 VBA 函数是 TextToColumns 函数。
Range("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
FieldInfo:=Array(1, xlDMYFormat)
I found the answer from this page - https://social.msdn.microsoft.com/Forums/en-US/e3522eac-13b1-476c-8766-d70794131cc8/strange-date-conversion-while-using-vba-replace-function?forum=isvvba
