How can I search and replace regular expressions in excel VBA macro?

Question

I would like to create a VBA macro that replaces all cells in a worksheet with text strings in time format (regular expression):

(1[0-2]|[1-9]):[0-5][0-9]:[0-5][0-9] [AP]M 

with cell address and sheet name. I think the call will be similar too:

  Cells.Replace What:="1:23:45 AM", Replacement:="=cell(""filename"")&cell(""Address"")", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False 

But I hope I can make the What:= argument an reg ex argument, or at least a limited time format.

How can i do this?

---

Test data: Save the following in CSV format:

 00:00,04:27,00:36,04:31,00:00 00:00,00:00,04:18,01:07,10:06 00:00,00:00,00:00,00:00,00:00 

In the end, the macro will delete all zero times and replace the other moments with static text, which is the estimated formula =cell("filename")&"!"&cell("address")

---

The result of the action on the above input file (I would save the sheets as XLSX):

  [ A ] [ B ] [ C ] [ D ] [ E ] [1] 'Sheet1!$B$1 'Sheet1!$C$1 'Sheet1!$D$1 [2] 'Sheet1!$C$2 'Sheet1!$D$2 'Sheet1!$E$2 [3] 

For brevity, I deleted the directory and file name returned by =cell("filename") , although this is what I really need.

Answer 1

I updated my code previously posted here to

• Delete all text fields in the user-selected range of '00:00
• Replace any text "time fields" with the full path

(nb: At the end, Regex overflows as a cell test for betwen values ​​of 0.0 and 1.0, sufficient for the actual data format)

  'Press Alt + F11 to open the Visual Basic Editor (VBE) 'From the Menu, choose Insert-Module. 'Paste the code into the right-hand code window. 'Press Alt + F11 to close the VBE 'In Xl2003 Goto Tools … Macro … Macros and double-click KillTime Sub KillTime() Dim rng1 As Range Dim rngArea As Range Dim lngRow As Long Dim lngCol As Long Dim lngCalc As Long Dim objReg As Object Dim strSht As String Dim X() On Error Resume Next Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8) If rng1 Is Nothing Then Exit Sub On Error GoTo 0 strSht = ActiveWorkbook.Path & "\[" & ActiveWorkbook.Name & "]" & rng1.Parent.Name 'remove '00:00 rng1.Replace "00:00", vbNullString, xlWhole 'See Patrick Matthews excellent article on using Regular Expressions with VBA Set objReg = CreateObject("vbscript.regexp") objReg.Pattern = "^0\.\d+$" 'Speed up the code by turning off screenupdating and setting calculation to manual 'Disable any code events that may occur when writing to cells With Application lngCalc = .Calculation .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With 'Test each area in the user selected range 'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on For Each rngArea In rng1.Areas 'The most common outcome is used for the True outcome to optimise code speed If rngArea.Cells.Count > 1 Then 'If there is more than once cell then set the variant array to the dimensions of the range area 'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks X = rngArea.Value2 For lngRow = 1 To rngArea.Rows.Count For lngCol = 1 To rngArea.Columns.Count If objReg.test(X(lngRow, lngCol)) Then X(lngRow, lngCol) = strSht & rngArea.Cells(1).Offset(lngRow - 1, lngCol - 1).Address(0, 0) Next lngCol Next lngRow 'Dump the updated array back over the initial range rngArea.Value2 = X Else 'caters for a single cell range area. No variant array required If objReg.test(rngArea.Value) Then rngArea.Value = strSht & rngArea.Address(0, 0) End If Next rngArea 'cleanup the Application settings With Application .ScreenUpdating = True .Calculation = lngCalc .EnableEvents = True End With Set objReg = Nothing End Sub 

Answer 2

Since you are trying to replace the format, I would replace it based on the format. With regex, it seems like you'll be forced to deal with a base number.

I tested this in XL 2003 and 2010:

 Sub ReplaceByFormat() With ActiveSheet.Cells .Replace What:="", Replacement:="=cell(""filename"")&cell(""Address"")", _ SearchFormat:=True, _ ReplaceFormat:=False, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Application.FindFormat.NumberFormat = "h:mm AM/PM" End With End Sub 

EDIT

Firstly, I had the error above when placing FindFormat at the end. It should be at the beginning (Doh).

There is no OR argument for the replace function. So below, I just repeated the code for the second type of format.

This code assumes dates are all constants. If they are formulas that you can fix using search and replace in vba. If they are a mix, you need to extend the code a bit:

 Sub ReplaceByFormat() With ActiveSheet.Cells.SpecialCells(xlCellTypeConstants) Application.FindFormat.NumberFormat = "h:mm AM/PM" .Replace What:="", Replacement:="=cell(""filename"")&cell(""Address"")", _ SearchFormat:=True, _ ReplaceFormat:=False, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Application.FindFormat.NumberFormat = "m/d/yyyy" .Replace What:="", Replacement:="=cell(""filename"")&cell(""Address"")", _ SearchFormat:=True, _ ReplaceFormat:=False, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub 

Answer 3

First, the best template would be:

  /[0-2]?[0-9]:[0-5][0-9]:[0-5][0-9] [A|P]M/ 

Secondly, the actual code without any special problems will be as simple as (adapting to your scenario, out of course):

 Set RegExp= CreateObject("VBScript.RegExp") RegExp.Pattern = "[0-2]?[0-9]:[0-5][0-9]:[0-5][0-9] [A|P]M" For i = ...... Expr = Format(ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(i, 1).NumberFormat) If RegExp.Test(Expr) Then Replace.... Next i 

Thus, VBA will process the values ​​in the cells as they appear.

EDIT

About the pattern also matching "29:00:00 | M" - I'm not sure why "|" is considered a valid char since it means "OR". The same thing happens if you use ",". Anyway, best of all:

 /^(([0-1]?[0-9])|(2[0-4])):[0-5][0-9]:[0-5][0-9] [A|P]M$/ 

• Allowed only 0-24 hours
• "^" and "$" ensures that the cell contains only the value of the time format in it, indicating the beginning and end of the line

But in the end, it doesn’t matter if the RegExp pattern EXACTLY matches only the time values, because the input data will be generated using Excel, which will first check the contents of the cell (if you type "29": 00: 00 "it will convert it to 5 am the next day.) This Excel solution can only lead to an Excel solution, but not to a global solution.

In this sense, using a regular expression is not even common in Excel - RegExp is a string tester without a semantic evaluation of the meaning, for which you have other ways of checking inputs in this context. For example, you can do the same with pure VBA:

 Function IsTime(rng As Range) As Boolean Dim sValue As String sValue = rng.Cells(1).Text On Error Resume Next IsTime = IsDate(TimeValue(sValue)) On Error GoTo 0 End Function 'Source: http://excel.tips.net/T003292_Checking_for_Time_Input.html