Replace numbers using regular expressions

[techiewithinme]

Hello,

I have a date value in the below format and I want to replace all numbers after seconds part with blank. How do I do that using regular expression?

10/4/2011 01:10:11:000300000 AM

Thanks.

 

[Rick Rothstein]

I was trying to replace thousands of date time values in one shot in Excel...QUOTE]

Sorry, this is not a RegExp solution, but it will still be quite fast in execution...

You can use this macro to do what you asked for...

Sub ReplaceJunkPartOfDateTimeText()
  Dim LastRow As Long, UnusedColumn As Long
  Const DataCol As Long = 1
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(StartRow, UnusedColumn).Resize(LastRow - StartRow + 1)
    .FormulaR1C1 = "=REPLACE(SUBSTITUTE(TRIM(RC" & DataCol & "),"":"","":="",3),LEN(RC" & DataCol & ")-2,1,""=:"")"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).Clear
    On Error GoTo 0
    .Replace ":=*=:", "", xlPart
    Cells(StartRow, DataCol).Resize(LastRow - StartRow + 1).Value = .Value
    .Clear
  End With
End Sub

The one thing you have to do before running it is to change the DataCol and StartRow constants (declared in the two Const statements) to match your actual setup. DataCol is the column number where your existing date-time text is located and StartCol is the first row number in that column containing your data.

 

[Rick Rothstein]

I was trying to replace thousands of date time values in one shot in Excel...QUOTE]

Sorry, this is not a RegExp solution, but it will still be quite fast in execution...

You can use this macro to do what you asked for...

<<< rest of message snipped>>>

I forgot to mention in my previous message... after you run my macro, your data should now be real Excel dates which you can then format in whatever way you like.