分享一个统一日期格式的工具

相对论R

【左图是未统一日期格式的数据，右图是代码运行后的数据】

【代码】

Function RemoveAllNonNums(myCell As String)
   Dim myChar As String
   Dim x As Integer
   Dim i As String
   i = ""
   v = Right(myCell, 1)
If Asc(v) < 48 Or Asc(v) > 57 Then
    w = Len(myCell) - 1
    myCell = Mid(myCell, 1, w)
End If
For x = 1 To Len(myCell)
    myChar = Mid(myCell, x, 1)
         If Asc(myChar) >= 48 And Asc(myChar) <= 57 Then
            i = i & myChar
            Else: i = i & "-"
         End If
Next
RemoveAllNonNums = i
End Function

---

Sub da()
o = Range("a65536").End(xlUp).Row
For k = 1 To o
    Range("B" & k).Select
    ActiveCell.FormulaR1C1 = "=RemoveAllNonNums(RC[-1])"
    Range("A" & k).Select
Next
Range("B1:B" & o).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False
Range("A1:A" & o).Select
Selection.NumberFormatLocal = "G/通用格式"
For j = 1 To o
    Range("a" & j) = Range("b" & j).Value
Next
Range("B1:B" & o).Delete
End Sub

【两段代码放入同一个模块运行即可，目前默认处理A列的数据，有其他需求的自行调整】

heathop

分列不香？！

haohu0406

谢谢楼主分享，很香