Remove punctuation but keep full number

[dac1517]

I have looked for a macro that will remove punctuation from column BQ, but all I have found thus far eliminates any 0's I have in front of the number. I need a macro that will remove punctuation but keep my number intact.

Numbers look: "001." need them to be "001"

Thanks for your help

 

[Richard Schollar]

Perhaps:

Sub test()
Dim cell As Range
With CreateObject("VBScript.RegExp")
  .Global = True
  .Pattern = "\D"
  For Each cell In Intersect(ActiveSheet.UsedRange, Range("BQ:BQ"))
    If Len(cell.Text) > 0 Then
      cell.NumberFormat = "@"
      cell.Value = .Replace(cell.Text, "")
    End If
  Next cell
End With
End Sub

 

[dac1517]

How can I apply this macro to an entire workbook and not each tab individually?

 

[Richard Schollar]

The below will run on column BQ in every sheet of the activeworkbook:

Sub test()
Dim cell As Range
Dim ws As Worksheet
With CreateObject("VBScript.RegExp")
  .Global = True
  .Pattern = "\D"
  For Each ws In Worksheets
    For Each cell In Intersect(ws.UsedRange, ws.Range("BQ:BQ"))
      If Len(cell.Text) > 0 Then
        cell.NumberFormat = "@"
        cell.Value = .Replace(cell.Text, "")
      End If
    Next cell
  Next ws
End With
End Sub

 

[dac1517]

I put this code under "This Workbook" on Visual Basic window and when run it gives me a "438" error. What could be causing this?

 

[Scott Huish]

The code should go in a standard module.

From the menu in the Visual Basic Editor (VBE), choose Insert, then Module.

 

[Richard Schollar]

I also had a typo in that code, corrected now above

 

[dac1517]

It still gives me the same error message, even as a module. The debug function identifies the below line of code. Is there something wrong with this line of code? If not, I need to go back to the drawing board and figure out my files problem.

For Each cell In Intersect(ws.UsedRange, .wsRange("BQ:BQ"))

 

[dac1517]

Values in column BQ are saved as text. Would this change the code?

 

[Richard Schollar]

Use the corrected code in post #4 - there is a typo in that line which is now corrected in #4