VBA Replace number with ""

[sachavez]

Is there a way to replace numbers with blanks.

Example:

.replace what:="number", replacement:=""

I want to replace all numbers....

Thanks!

 

[Joe4]

One way is to loop through the range that you want to apply this to, and check to see if the entry is numeric with the IsNumeric function, i.e.

Sub MyReplace()

    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Set range to apply this to
    Set rng = Range("A1:C5")
    
'   Loop through all cells in range
    For Each cell In rng
        If IsNumeric(cell) Then cell.ClearContents
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[sachavez]

Does this clear the entire cell? I have other contents in the cell that I'd like to keep. Just want the numbers out.

Thanks!

One way is to loop through the range that you want to apply this to, and check to see if the entry is numeric with the IsNumeric function, i.e.

Sub MyReplace()

    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Set range to apply this to
    Set rng = Range("A1:C5")
    
'   Loop through all cells in range
    For Each cell In rng
        If IsNumeric(cell) Then cell.ClearContents
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[JoeMo]

Is there a way to replace numbers with blanks.

Example:

.replace what:="number", replacement:=""

I want to replace all numbers....

Thanks!

Does ALL numbers include numbers returned by formulas or just numeric constants? If formulas are included do you want to replace the formulas with blanks?

 

[sachavez]

for clarity, I'd like to keep all non-number text in each cell. Just want the numbers gone.

Is there a way to replace numbers with blanks.

Example:

.replace what:="number", replacement:=""

I want to replace all numbers....

Thanks!

 

[sachavez]

I ended up writing ten of these:

.replace what:="0", replacement:=""

one for each number. It worked.

 

[Joe4]

What exactly do your entries look like?

Your question was rather ambiguous, and it is not clear to me if you just want to clear cells that are ALL NUMERIC, or you may have entries like "AB123XY" and you want to replace just the numbers so that you are left with "ABXY".

 

[Joe4]

I am assuming that you want to replace the numbers in mixed entries, like "AB123XY" (since my original code did not seem to work for you), here is some code that will instead of having to write out 10 replacement commands, will loop through all 10 numbers.

Sub MyReplaceNums()

    Dim rng As Range
    Dim cell As Range
    Dim nums As String
    Dim rep As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Set values to replace
    nums = "0123456789"
    
'   Set range to do replacements
    Set rng = Range("A1:A10")
    
'   Loop through all cells in range
    For Each cell In rng
'       Loop through all numbers
        For i = 1 To Len(nums)
'           Get number to replace
            rep = Mid(nums, i, 1)
'           Do replacement
            With cell
                .Replace what:=rep, replacement:=""
            End With
        Next i
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub