Excel Function to Remove All Text OR All Numbers from a Cell

Edit
Add to Favorites
Author:

How to create and use a function that removes all text or all numbers from a cell, whichever you want.

To do this, we will create a UDF or User Defined Function in Excel.

But, I won't make you start from scratch; if you want to get the functions right away, simply download the Excel file attached to this tutorial and copy the code for yourself or get it from below.

Sections:

The Functions

How to Install and Use these Functions

Notes

The Functions

If you just want the code and already know what to do with a UDF, here it is.

UDF to Remove All Text from a Cell

Function KillText(text_value As Range)

   

    ValueInput = text_value.Value

   

    ValueResult = ""

   

    n = Len(ValueInput)

   

    For i = 1 To n

   

        InputCharacter = Mid(ValueInput, i, 1)

       

        If InputCharacter Like "[0-9]" Then

       

            ValueResult = ValueResult & InputCharacter

           

        End If

       

    Next i

       

    KillText = ValueResult

       

End Function

UDF to Remove All Numbers from a Cell

Function KillNumbers(text_value As Range)

   

    ValueInput = text_value.Value

   

    ValueResult = ""

   

    n = Len(ValueInput)

   

    For i = 1 To n

   

        InputCharacter = Mid(ValueInput, i, 1)

       

        If InputCharacter Like "[A-Za-z]" Then

       

            ValueResult = ValueResult & InputCharacter

           

        End If

       

    Next i

       

    KillNumbers = ValueResult

       

End Function

How to Install and Use these Functions

In order to get these functions to work, we first need to install them into Excel.

  1. Hit Alt + F11 to go to the VBA Editor window.
  2. Once there, go to Insert > Module

  3. You will see an empty window like this:
  4. Paste the code from the previous section into this window:
  5. Go back to Excel, Alt + F11, and then simply start typing either KillNumbers or KillText into a cell and you should see the functions appear in the function drop down menu.
    Select the desired function and then select the cell that contains the data and hit enter, just like you would enter a regular function in Excel.

  6. You can see the output from both functions here:

That's all there is to it!

Notes

To kill the text and numbers from a cell, I created a UDF or User Defined Function. This is basically a macro that allows for the creation of a custom function in Excel.

Check out the tutorial on User Defined Functions in Excel for more information on those.

Make sure to download the sample file attached to this tutorial so you can copy/paste the code with ease and see the examples at work in Excel.

Question? Ask it in our Excel Forum


Excel Version: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016
Downloadable Files: Excel File
Similar Content on TeachExcel
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Simple Excel Function to Extract a Word or Text from a Cell
Tutorial: Excel function that makes it easy to extract a word or text from a cell in Excel. This is ...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
PV Function - Get the Present Value in Excel
Tutorial: The Present Value (PV) function in Excel will return the current value of an investment.Â...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
Simple Excel Function to Combine Text with a Separator
Tutorial: Excel function that makes combining text very easy. This function is simpler and better th...