Macro to delete all text from cells and leave numbers

[mrmonk21]

I need a macro to delete all text from cells and leave the numbers

Please help

 

[mrmonk21]

Thats brilliant.

Many thanks

 

[Rick Rothstein]

Here is another macro for you to try (change the two Const, short for constant, statements to match your actual data setup)...

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "A"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If Mid(CellVal, Z, 1) Like "[!0-9]" Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

 

[Ron Coderre]

You're very welcome..

Since you know exactly what you want to do (strip all non-numeric characters from cells), perhaps you could just use this abbreviated macro:

Sub LeaveNumbers()
Dim cCell As Range
Dim RE As RegExp
 
Set RE = New RegExp
RE.Pattern = "\D"
RE.Global = True
 
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & RE.Replace(cCell.Text, "")
    End If
Next cCell
End Sub

Note: you'll still need the reference to the Microsoft VBScript Regular Expressions resource

I hope that helps.

 

[jeffreybrown]

This one uses a helper cell, but it's still worth a look. Also, the flexibility to reverse the logic: i.e., only numbers or only text.

Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=362461
' =TextNum(A1,1)
' 1 for Text only
' 0 for Numbers only
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(ref = True, "\d+", "\D+")
        .Global = True
        TextNum = .Replace(txt, "")
    End With
End Function

 

[Rick Rothstein]

This one uses a helper cell, but it's still worth a look. Also, the flexibility to reverse the logic: i.e., only numbers or only text.

Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=362461
' =TextNum(A1,1)
' 1 for Text only
' 0 for Numbers only
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(ref = True, "\d+", "\D+")
        .Global = True
        TextNum = .Replace(txt, "")
    End With
End Function

If a UDF appeals to the OP, then here is the non-RegEx version of your function for him to consider...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  ' Ref argument... 1 for Text only, 0 for Numbers only
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If Mid(Txt, X, 1) Like "[" & Left("!", 1 - Ref) & "0-9]" Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

 

[jeffreybrown]

here is the non-RegEx version

Hi Rick,

Can you explain what exactly a non-RegEx version means?

 

[Rick Rothstein]

Hi Rick,

Can you explain what exactly a non-RegEx version means?

Maybe I should have written it as non-RegExp? I was just referring to the fact that the function I posted uses native VB functions only and does not rely on a Regular Expression engine... the CreateObject("VBScript.RegExp") in your function... to perform its action.

 

[drgs]

what about negative and decimal numbers

 

[jeffreybrown]

Okay I understand...Thanks

 

[pgc01]

If a UDF appeals to the OP, then here is the non-RegEx version of your function for him to consider...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  ' Ref argument... 1 for Text only, 0 for Numbers only
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If Mid(Txt, X, 1) Like "[" & Left("!", 1 - Ref) & "0-9]" Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

Hi Rick

I get the same result for Ref = True/False. Maybe you meant:

 ... & Left("!", 1 [B][COLOR=red]+[/COLOR][/B] Ref) & ...

or

 ... & IIf(Ref, "", "!") & ...