text and numbers in cell

[Henk]

I want to delete the text in 30.000 cells from a cell which contains text and
numbers. What is the easiest way to do so?

Thanks

[Marcelo]

Hi Henk

Copy this code and use the function Digitsonly(a2), copy down, copy and
paste values

hth
regards from Brazil
Marcelo

*****************************************************
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
******************************************************



"Henk" escreveu:

> I want to delete the text in 30.000 cells from a cell which contains text and
> numbers. What is the easiest way to do so?
>
> Thanks

[Gord Dibben]

Henk


Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.-]" Then
'change to [0-9] if dec. pt. and hyphen not required
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben MS Excel MVP


On Wed, 9 Aug 2006 06:43:02 -0700, Henk <[email protected]> wrote:

>I want to delete the text in 30.000 cells from a cell which contains text and
>numbers. What is the easiest way to do so?
>
>Thanks