How to remove some numbers from a cell in Excel

By SpreadCheaters
Page last updated: 17/03/2023 | Next review date: 17/03/2025
close

Author bio


SpreadCheaters
View More north_east

Excel is a powerful tool that allows you to manipulate data in various ways. One common task you may need to perform is removing some numbers from a cell. For example, you may have a cell that contains a combination of text and numbers, and you want to remove the numbers to extract only the text.

Here we have a dataset shown above that contains Employee Names and Employee IDs. In this tutorial, we will learn how to remove numbers from the Employee ID column using two different methods by following the steps below but first let’s have a look at the dataset.

Method 1: Find & Select Method

This method will be useful when you want to remove the numbers from the cells that contain only the numbers/digits.

Step 1 – Open Find & Select menu.

  • Select the dataset and open the dropdown menu of Find & Select in the Editing group.
  • In the menu click on Go To Special.
  • You can also press Ctrl+G then Alt+S to open GoTo Special Menu.

Step 2 – Select the Numbers.

  • To select the cells with only numbers in the Go To Special menu select Constants.
  • Check the Numbers box and uncheck the others.
  • Click Ok.
  • Cells that contain only numbers will be selected.

Step 3 – Delete the cells.

  • Once the cells with only numbers have been selected, go to the Cells group.
  • Click on Delete.
  • You can also press Alt + Delete.

Method 2: Use Nested SUBSTITUTE Method

The second method uses Nested SUBSTITUTE functions to replace any occurrences of a digit in a cell which contains alphanumeric data.

Step 1 – Type SUBSTITUTE Formula.

  • Select the cell where you want to type the SUBSTITUTE formula.
  • Syntax of the formula is

SUBSTITUTE(Cell_address,Value_to_Substitute,”Value_to_be_replaced_with_Substituted_value ”)

  • As we have to remove numbers that can be ranging from 0 – 9 therefore we will nest the Substitute function to substitute multiple numbers.
  • In our case, the formula will be

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,1,””),2,””),3,””),4,””),5,””),6,””),7,””),8,””),9,””),0,””)

  • When this formula will be applied to the cells any occurrence of numbers in cells will be deleted.

Step 2 – Find values for the rest of the cells. 

  • Select the cell with the formula.
  • Drag that cell from the bottom right to the rest of the cells.
  • Their values will appear automatically.