Formula to remove non-numeric characters from a cell

[Mr. Moxie]

I need a formula to remove all non-numeric characters from a cell... For
example if the cell contains (207)555-1212 I would want 2075551212,
or if I had 123-456A, I would want 123456.

Does anyone have a formula that would work?

Thanks in advance.
D.

 

[Ron Rosenfeld]

I need a formula to remove all non-numeric characters from a cell... For
example if the cell contains (207)555-1212 I would want 2075551212,
or if I had 123-456A, I would want 123456.

Does anyone have a formula that would work?

Thanks in advance.
D.

One method:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula:

=REGEX.SUBSTITUTE(A1,"\D")

The formula will return the digits as a text string. If you need the result as
a "number", then precede the formula with a double unary to convert it:

=--REGEX.SUBSTITUTE(A1,"\D")


--ron

 

[Gord Dibben]

One method.

Copy this UDF to your workbook.

Function DeleteNonNumerics(ByVal sStr As String) As Long
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

=DeleteNonNumerics(cellref)


Gord Dibben MS Excel MVP