MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

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!!



3 Answers Found

Answer #1    Answered By: Lonnie Rogers     Answered On: Mar 06

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 ###

Answer #2    Answered By: Hubba Akhtar     Answered On: Mar 06

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

Answer #3    Answered By: Sumitra 2004     Answered On: Mar 06

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.