VBA - Remove numbers from column

[jamescooper]

Hello,

This code removes the "/" from all cells in a column, how would I adapt to remove numbers please?

Sheets("Sheet3").Columns("B:B").Replace What:="/", Replacement:="", LookAt:=xlPart

 

[Rick Rothstein]

Hello,

This code removes the "/" from all cells in a column, how would I adapt to remove numbers please?

Sheets("Sheet3").Columns("B:B").Replace What:="/", Replacement:="", LookAt:=xlPart

There is no single line solution for this. The following code is probably about the fastest code you will find to process the cells in Column B...

Sub RemoveNumbersFromColumnB()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[0-9]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Replace(Data(R, 1), " ", "")
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub

 

[jamescooper]

There is no single line solution for this. The following code is probably about the fastest code you will find to process the cells in Column B...

Sub RemoveNumbersFromColumnB()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[0-9]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Replace(Data(R, 1), " ", "")
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub

Thanks, it took all the numbers out of the cells but also took all the spaces out between the text too! Any ideas? Thanks.

 

[Rick Rothstein]

Thanks, it took all the numbers out of the cells but also took all the spaces out between the text too! Any ideas? .

Maybe this...

Sub RemoveNumbersFromColumnB()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[0-9]" Then Mid(Data(R, 1), X) = Chr(1)
    Next
    Data(R, 1) = Application.Trim(Replace(Data(R, 1), Chr(1), ""))
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub

 

[jamescooper]

Maybe this...

Sub RemoveNumbersFromColumnB()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[0-9]" Then Mid(Data(R, 1), X) = Chr(1)
    Next
    Data(R, 1) = Application.Trim(Replace(Data(R, 1), Chr(1), ""))
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub

Thanks, works great. Thanks Rick.