VBA - Remove numbers from column

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
840
Hello,

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

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hello,

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

Code:
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...
Code:
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
 
Upvote 0
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...
Code:
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.
 
Upvote 0
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...
Code:
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
 
Upvote 0
Maybe this...
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top