Remove all characters but numbers

B

beeawwb

Good afternoon all,

Just wondering if it's possible to take a range of cells, and remov
all characters from the cell(s) (including spaces, etc) and leave onl
numerical characters... but still leave the cells formatted as text.

I assume that I would be using VBA, and there would be some thin
like...

Range("B1:B100")
For Number <= Length(Cell)
If Char(Number) <> (1 or 2 or 3 etc) Then
Char(Number)=""
Number=Number+1
End If
Next Cell

Or something like that. My psuedocode is a little better than my rea
coding knowledge, and I don't know if any of those codes actuall
exist.

Thanks for your help on this one,

-Bo
 
N

Norman Jones

Hi beeawb,

Try:

Sub Tester()
Dim cell As Range
Dim rng As Range

Set rng = ActiveSheet.Range("B2:B20") '<========= CHANGE!!

For Each cell In rng
cell.Value = DeleteNonNumerics(cell.Value)
cell.NumberFormat = "@"
Next cell

End Sub

Function DeleteNonNumerics(ByVal sStr As String) As String
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If

End Function

Adjust the line:
Set rng = ActiveSheet.Range("B2:B20")
to suit your requirements.
 
S

Soo Cheon Jheong

Hi,

Sub TEST()

Dim i As Long
Application.ScreenUpdating = False
With Range("B2:B2000")
.NumberFormat = "@"
For i = 1 To 10
.Replace What:=Right(CStr(i), 1), Replacement:="",
LookAt:=xlPart
Next
End With
Application.ScreenUpdating = True

End Sub


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--
 
D

Dave Peterson

I think you're doing just the opposite of what the OP requested <bg>.

And I bet you really meant:
for i = 0 to 9
to get rid of the numbers.
 
B

beeawwb

Thanks for the help guys, used Norman's solution, worked perfectly. :)

Many thanks once again,

-Bo
 
Top