Search:

# Delete everything in a workbook that is a number

Asked By: Sienna    Date: Mar 06    Category: MS Office    Views: 1675

I have a workbook with a bunch of characters and numbers

I need to remove the character and leave the numbers?

Any ideas would be greatly appreciated!!

Share:

I am not sure if this what you want.

I am assuming you have a column with letters and numbers in each cell (e.g.
"xyz147s" and you want to show "147".

Put the following function in a module - that is a module!!! you need to add a
module to the project in the VBE.

Paste the text in

Look for any red lines where something has got corrupted and correct them.

Then in another column enter the formula stripnumbers(A1) if A1 is the first
cell you want to deal with.

Copy the formula down the length of the column

If you want to fix the results just select, copy, and paste special, values.

If you want to do something else please explain more precisely.

The subject and content seem to be talking about opposite aims.

'### CODE ###
Option explicit

Function stripletters(source As String) As String

'Returns a string comprising only the numbers
target = ""
For x = 1 To Len(source)
thisletter = Mid(source, x, 1)
If Asc(thisletter) > 47 And Asc(thisletter) < 58 Then
target = target & thisletter
End If
Next x
stripletters = target

End Function

'### End of Code ###

Sub NoNumbers()
Dim i As Integer
Dim j As Integer
Dim iLastRow As ing
Dim iLastCol As Integer

'find last row, last column number  -- left as an exercise

For i = 1 To iLastRow
For j = 1 To iLastCol
If WorksheetFunction.IsText(Cells(i, j).Value) = True Then _
Cells(i, j).Value = vbNullString
Next j
Next i

End Sub

Thank you very much-I will give this a try!

Didn't find what you were looking for? Find more on Delete everything in a workbook that is a number Or get search suggestion and latest updates.