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

 

[Ron Coderre]

Can you clarify?
If some cells contain numbers and others contain text, you may not need a vba solution
...try this:
• Select the range to be impacted...e.g. A1:Z100
• F5...shortcut to: goto
• Click: special cells
• Check: Constants...leave only TEXT checked (so, uncheck the others)
• Click: OK
Now only the text cells are selected
• Press: DELETE

If the cells contain alphanumerics (like 123abc456), that's more complicated and will require VBA

 

[mrmonk21]

Here are some examples. Some have just numbers most of them have text as well.

07979 857798
07980634988, 07984439104
07985 349 427
07985 349427
07989 422222
0844 567 0578
0844 567 0578 Ref Lap1374099044
44 (0) 7737 987860
A on 07908855297
A on 07957797966
Aaron on 07899905803
Aaron on 07985618246
Abbey on 07970501079
abdalla on 07771588190
Abdul on 07779100309
Abeed on 07957105751

 

[Ron Coderre]

Here are some examples. Some have just numbers most of them have text as well.

07979 857798
07980634988, 07984439104
07985 349 427
07985 349427
07989 422222
0844 567 0578
0844 567 0578 Ref Lap1374099044
44 (0) 7737 987860
A on 07908855297
A on 07957797966
Aaron on 07899905803
Aaron on 07985618246
Abbey on 07970501079
abdalla on 07771588190
Abdul on 07779100309
Abeed on 07957105751

Thanks for the additional information. Can you post what values you'd like to see, based on that sample?

 

[mrmonk21]

I just want the numbers left.


Remove all text.

Thanks in advance

 

[Ron Coderre]

Also...What version of Excel are you using?

 

[mrmonk21]

excel 2007

 

[Ron Coderre]

I just want the numbers left.


Remove all text.

Thanks in advance

So, are you saying you want the new cell contents to be this?:
07979857798
0798063498807984439104
07985349427
07985349427
07989422222
08445670578
084456705781374099044
4407737987860
07908855297
07957797966
07899905803
07985618246
07970501079
07771588190
07779100309
07957105751

 

[mrmonk21]

yes thats perfect

 

[Ron Coderre]

OK...Here's what to do

• Select the workbook you want to contain the program
• ALT+F11...to open the VBA editor
• Insert.Module...to include a new general module in that workbook
• Tools.References
...Find one of the Microsoft VBScript Regular Expressions resources and check it, then click: OK
(I used: Microsoft VBScript Regular Expressions 5.5)
• Copy the below code and paste it into the new module:

Public Function PullOnly(strSrc As String, CharType As String)
Dim RE As RegExp
Dim regexpPattern As String
Set RE = New RegExp
CharType = LCase(CharType)
Select Case CharType
    Case Is = "digits":
        regexpPattern = "\D"
    Case Is = "letters":
        regexpPattern = "\d"
    Case Else:
        regexpPattern = ""
End Select
RE.Pattern = regexpPattern
RE.Global = True
PullOnly = RE.Replace(strSrc, "")
End Function
 
Sub LeaveNumbers()
Dim cCell As Range
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & PullOnly(cCell.Text, "digits")
    End If
Next cCell
End Sub

• Select the data range to be impacted in your worksheet
• ALT-F8...to see the list of available macros
• Select: LeaveNumbers
• Click: Run

Is that something you can work with?