

Getting Rid of Everything Except Numbers

by Allen Wyatt
(last updated June 21, 2018)


Linda has a column that contains alpha and numeric characters. She needs to retain the numeric characters and delete the alpha ones. For example, a cell may contain 10003E111 and she wants to end up with 10003111.

There are a few ways you can approach this problem. Before proceeding with any solution, however, you should make sure that you aren't trying to change something that isn't really broken. For instance, you'll want to make sure that the "E" that appears in the number isn't part of the format of the number—in other words, a designation of exponentiation. If it is, then you don't really want to remove the character because it will end up changing the nature of the underlying number.

If you determine that the characters aren't part of the number's format, then you can first try using formulas to remove the alpha characters. If the values you want to change are in column A, you could enter the following (very long) formula in column B:


Make sure you enter this as an array formula by pressing Ctrl+Shift+Enter. Then enter the following into column C:


The result is that column C contains the values from column A, without the alpha characters. You could use Paste Special to copy the information from column C to another column so that you end up with actual values instead of formula results.

This approach may work great for short-term use on a single workbook, but if you need to do this sort of data processing more often then you will want to create a user-defined function to do the processing. Here's an example:

Function OnlyNums(sWord As String)
    Dim sChar As String
    Dim x As Integer
    Dim sTemp As String

    sTemp = ""
    For x = 1 To Len(sWord)
        sChar = Mid(sWord, x, 1)
        If Asc(sChar) >= 48 And _
          Asc(sChar) <= 57 Then
            sTemp = sTemp & sChar
        End If
    OnlyNums = Val(sTemp)
End Function

You use this function by calling it from within a worksheet cell:


The function returns a numeric value. If you want to create an even shorter macro to do the processing, consider the following:

Function StripChar(aText As String)
    Dim I As Integer

    StripChar = ""
    For I = 1 To Len(aText)
        aChar = Mid(aText, I, 1)
        Select Case aChar
            Case "0" To "9"
                StripChar = StripChar & aChar
        End Select
End Function

To use this function, use either of the following in your worksheet:


The first returns a text string consisting of the digits, the second returns the numeric version of that string.



ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3840) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Getting Rid of Everything Except Numbers.

Author Bio

Allen Wyatt



Locking Icons on the Desktop

Your desktop is supposed to be place where you can put your most-often-used program and file icons. You can spend hours ...

Discover More

Sorting with Graphics


Discover More

Creating an Index Entry for a Range of Pages


Discover More


More ExcelTips (menu)

Deleting All Names but a Few


Discover More

Finding Unknown Links


Discover More

Undoing an Edit


Discover More


View most recent newsletter.



What is three minus 0?

2019-09-16 11:24:39

Kevin Mitchell

The formula to strip non-numerics also sees decimal points as non-numeric, and splits on the decimal.

2018-09-25 15:23:14

Stephen Sherry

Apologies for the double post but I got my version fixed.

'As Variant' instead of 'As String' solved it.

Not sure how speedy it is but I'm good with regular expressions so it's very customizable.

Function StripChar(Txt As String) As Variant
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = Val(.Replace(Txt, " "))
End With
End Function

2018-09-25 15:07:48

Stephen S

Hi, The 1st fn works well. But I'm having trouble with this. It's using regexp. I just what the fn to return a value and not a string. Thanks in advance.

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
StripChar = Val(Txt)
End Function

2018-01-21 03:23:01

Michael (Micky) Avidan

@To whom it may concern,
To my opinion - the suggested formula can be at least 50% shorter.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator

2018-01-20 08:49:27

TC Pun

Thank you very much. The formula works!

This Site


Newest Tips



View the most recent newsletter.