Remove all Non Numeric Characters from a cell

engarea,

Try using:

=MID(A1,FIND({"1","2","3","4","5","6","7","8","9","0"},A1,1),LEN(A1)-FIND({"1","2","3","4","5","6","7","8","9","0"},A1,1)+1)

Patrick

Engarea

This code kills non numerics and non spaces

- press Alt & F11 to go to the VBE
- Insert Module
- paste the code below

then press Alt & F11 to return to Excel
select your range of interest
Tools - Macro - Macros and doubleclick KillNonNum

Sub KillNonNum()
    Dim C As Range, RegEx, i As Long
    Set RegEx = CreateObject("vbscript.regexp")
    RegEx.Global = True
    RegEx.Pattern = "[^\d\s]+"
    For Each C In Selection
        C.Value = RegEx.Replace(C.Value, "")
    Next
    Set RegEx = Nothing
End Sub


Cheers

Dave

No thats not working, giving me the old #VALUE return

there is an error with line,

C.Value = RegEx.Replace(C.Value, "")

Also can you display this as a function so i can type into a cell for example:

=killnonnum("and the chosen cell here")

Yeh that is working but need it as a function not a sub

Cheers engarea

i have increased points to accomodate changing it to a function so i can using as a cell formulae

Brett,

That's a beaut! The only hitch I found in that is that it preserves embedded spaces.
Would like to remove them too?

- stochastic

Hi stochastic,

If you want to remove all spaces change

 RegEx.Pattern = "[^\d\s]+"
to
 RegEx.Pattern = "[^\d]+"

Cheers

Dave

okay, got it. thanks.
in the first case, "remove everything except digits or spaces"
in the second case, "remove everything except digits".

- sto