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

 

[Manev]

If that is what your data looks like, you do not need a macro... simply select the column or columns with that data, press CTRL+H to bring up Excel's Replace dialog box, type a space followed by an asterisk in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the checkbox labeled "Match entire cell contents" is not checked, then click the "Replace All" button.

Wow! That worked like magic. Many thanks for your help.

 

[ChrisJohn]

Wow! That worked like magic. Many thanks for your help.

Hello, I am new to this thread (and VBA) but found it and registered after attempting unsuccessfully trying to write my own script for the first time so please be patient with me! Any help would be appreciated. When I tried the macro it left me with the number in a text form rather than numerical i.e. with a " ' " in front of the number. What I am actually trying to do is this; my bank offers online statements but has recently changed the formatting so that text appears in the final balance column when I copy and paste into an Excel workbook. They also have extra blank rows between each entry. I would like to be able to remove the text and leave the number in accounting format such as £ 6,725.69. Removing the extra rows would be a bonus. Thanks.

Two examples of the balance cell are these:

6,725.69Click enter to display the details of the transaction

[TABLE="width: 72"]
<tbody>[TR]
[TD] 6,742.69Click [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[jordanburch]

Ron,

I didn't think my original Like statement line was all that bad

If Mid(CellVal, Z, 1) Like "[!0-9]" Then Mid(CellVal, Z, 1) = " "

however we could have used this statement in its place instead...

If InStr("0123456789", Mid(CellVal, Z, 1)) = 0 Then Mid(CellVal, Z, 1) = " "

Actually, in thinking about it, the InStr statement is probably a touch faster than the Like version. Here is my original macro using Instr instead of Like...

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 InStr("0123456789", Mid(CellVal, Z, 1)) = 0 Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

And, this InStr version can be used in my UDF as well...

Function TextNum(ByVal Txt As String, Optional Ref As Boolean = False) As String
  Dim X As Long, CellVal As String
  For X = 1 To Len(Txt)
    If InStr("0123456789", Mid(Txt, X, 1)) > 0 = Ref Then Mid(Txt, X, 1) = " "
  Next
  TextNum = Replace(Txt, " ", "")
End Function

There, no Like operator... I guess that is better then, right?

This worked like a charm thanks!