如何批量把文件格式转换成日期格式

2024-12-25 21:53:29

推荐回答（3个）

回答1：

是word中就好办了
Sub 日期格式处理() '14:37:14 :05 2006-04-14 09:07:05
On Error Resume Next
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting '2011/5/9 转变
With Selection.Find
.Text = "20([0-9]{2,3})/{1}([0-9]{1,2})/{1}([0-9]{1,2})"
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "\( ([0-9]{2})-([0-9]{1,2})-([0-9]{2}) ([0-9]{2}):([0-9]{2})\)"
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "【字体：? ? ?】"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})- ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})" '格式 2010-12-07 15:46:47
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})" '格式 2010-12-07
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})- ([0-9]{1,2}):([0-9]{1,2})" '格式 2010-12-07 15:46
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日 ([0-9]{1,2}):([0-9]{1,2}):" '格式 2010年8月13日 18:25
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日 ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})" '格式 2010年8月13日 18:25:38
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日 ([0-9]{1,2}):([0-9]{1,2})" '格式 2010年8月13日 18:25
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})" '格式 2010年8月13日18:25:38
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日([0-9]{1,2}):([0-9]{1,2})" '格式 2010年8月13日18:25
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})" '格式 2010-08-13 18:25:38
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2})" '格式 2010-8-13 18:25 换成 10-08-13
.Replacement.Text = "\1-\2-\3"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "20([0-9]{1,2})年([0-9]{1,2})月([0-9]{1,2})日" '格式2010年8月13日换成 10-08-13
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p10-"
.Replacement.Text = "_10-"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})" '14:32:48
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "\([(0-9)]{6})\)" '格式 (000597)
.Replacement.Text = "_"
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^13([0-9]{2})-([0-9]{2})-([0-9]{2})"
.Replacement.Text = " \1-\2-\3"
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([0-9]{2})年([0-9]{2})月([0-9]{2})日 ([0-9]{2})：([0-9]{2}) "
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "([0-9]{2})年([0-9]{2})月([0-9]{2})日 ([0-9]{2})：([0-9]{2}) 来源："
.Replacement.Text = "\1-\2-\3 "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " ([0-9]{2}):([0-9]{2})"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^13\(([0-9]{1,3})/([0-9]{1,4})\)"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

End Sub

回答2：

所以，你文本中如果是存储的2009-12-13的话可以：[ 单元格（右键）-》设置单元格格式-》日期-》XXXX年XX月XX日 ]。来实现，如果表格中是2009.12.13

回答3：

假如你的数据在A1开始的列中，在后面一空列中输入：
=text(a1,"yyyy-mm-dd")
向下复制就可以了。