Premium Excel Course Now Available!
Build Professional - Unbreakable - Forms in Excel
45 Tutorials - 5+ Hours - Downloadable Excel Files
Excel Function to Remove All Text OR All Numbers from a Cell
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:
How to Install and Use these Functions
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.
- Hit Alt + F11 to go to the VBA Editor window.
- Once there, go to Insert > Module
- You will see an empty window like this:
- Paste the code from the previous section into this window:
- 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.
- 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
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Tutorial: Excel function that makes it easy to extract a word or text from a cell in Excel. This is ...
Tutorial: The Present Value (PV) function in Excel will return the current value of an investment.Â...
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
Tutorial: Excel function that makes combining text very easy. This function is simpler and better th...
Tutorial: This is a practical guide to using Variables in Macros and VBA for Excel. I will tell you ...
