How to Remove Letters from Cell in Excel (10 Methods)

In this article, you will learn how to remove letters from a cell in Excel.


Download Workbook

You can download the free practice Excel workbook from here.


10 Methods to Remove Letters from Cell in Excel

This section will discuss how to remove letters from a cell in Excel using Excel’s command tools, various formulas, VBA etc.

1. Remove Specific Letters from Cell with the Find and Replace Feature in Excel

The Find & Replace command is the easiest and the most common feature to do most of the Excel-related tasks. Here we will get to know how to delete characters by using the Find & Replace feature in Excel.

Consider the following dataset from where we will strip out all the letters (WWE) from the cells in the Code column leaving only the numbers.

The steps to do that are given below,

Steps:

  • Select the dataset.
  • Under the Home tab, go to Find & Select -> Replace.

Remove Specific Letters from Cell with the Find and Replace Feature in Excel

  • From the pop-up Find and Replace box, in the Find what field, write WWE.
  • Leave the Replace with field blank.

  • Press Replace All.

Result of Remove Specific Letters from Cell with the Find and Replace Feature in Excel

This will erase all the WWE from the cells in your dataset in Excel and leave you with only the numbers.


2. Delete Specific Letters from Cell with the SUBSTITUTE Function in Excel

Unlike the Find & Replace command feature in Excel, using formula is the safest and the most controlled way to extract any kind of results in Excel. To get the output of a dataset without any specific character in Excel, you can implement the SUBSTITUTE function.

Generic SUBSTITUTE Formula,

=SUBSTITUTE(cell, "old_text", "new_text")

Here,

old_text = the text you want to remove

new_text = the text that you want to replace with

Below is the same dataset that we used in the above section. And this time, instead of using the Find & Replace feature to remove letters, we are going to apply the SUBSTITUTE function to get the desired output.

Steps:

  • In an empty cell where you want your result to appear, write the following formula,
=SUBSTITUTE(C5,"WWE","")

Here,

C5 = cell that contains value to strip out the letters
"WWE" = the letters to remove
""= to replace “WWE” with empty string

  • Press Enter.

Remove Specific Letters from Cell with the SUBSTITUTE Function in Excel

It will replace all WWE (or any other text that you selected) with a null string (or the string that you replace it with).

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

Remove Specific Letters from Cell with the SUBSTITUTE Function apply in Excel

Now you have found the result of a dataset of cells without any letters.


3. Extract Letters from a Particular Instance from Cell in Excel

Till now we were only learning how to remove all letters from cells. But what if you want to delete letters only from a certain position of the cells.

Such as, instead of removing all WWE from the cells, we want to keep only the 1st W along with the numbers from each cell.

Steps:

  • Just like the above section where we implemented the SUBSTITUTE function to remove WWE, here we will just define the certain position from which we want to remove the letters.
    So the above SUBSTITUTE formula,
=SUBSTITUTE(C5,"WWE","")

Becomes,

=SUBSTITUTE(C5,"WE","",1)

Here, 1 means, we want to remove the 1st W from the cells of our dataset (if you want to remove the 2nd letter from your dataset then just write 2 instead of 1, if you want to remove the 3rd letter from your dataset then just write 3 instead of 1, and so on).

  • Press Enter.

Remove Letters from a Particular Instance from Cell in Excel

  • Again, just drag the row down using Fill Handle to apply the formula to the rest of the cells.

Now you have found the result of a dataset of cells with the 1st W along with the numbers.


4. Delete Multiple Specific Letters from Cell with the Nested SUBSTITUTE Function

The SUBSTITUTE function only removes letters for any number of instances at a time. So, if you want to remove multiple letters at once then you need to implement the nested SUBSTITUTE function.

So let’s find out how to implement the nested SUBSTITUTE function to remove multiple letters at once.

Steps:

  • To establish a nested SUBSTITUTE function, you have to write a SUBSTITUTE function inside another SUBSTITUTE function and pass relevant arguments inside the brackets.
    To understand more, check the picture below,

Remove Multiple Specific Letters from Cell with the Nested SUBSTITUTE Function in Excel

Where,

To remove multiple W from the C5 Cell, first we write the formula,

=SUBSTITUTE(C5,"W","")

and then, to delete E (or any other letter that you required) along with it, we put this formula inside another SUBSTITUTE formula and pass the arguments (old_text, new_text) inside it (in our case, it was "E","").

So, now the formula is,

=SUBSTITUTE(SUBSTITUTE(C5,"W",""),"E","")
  • Press Enter.
    It will replace all W and E (or any other text that you selected) with a null string (or the string that you replace it with).
  • Once again drag the row down using Fill Handle to apply the formula to the rest of the cells.

Now you have found the result of a dataset of cells without any letters.


5. Strip First or Last Letters from Cell with Formula in Excel

In this section, you will learn how to remove the first or last letters from cells with the formula in Excel.

5.1 Delete First Letters from Cell with Formula in Excel

Steps to delete first letters from cells with the formula in Excel are given below.

Steps:

  • First, select a cell that you want your result to be shown.
  • In the cell, write the following formula,
=RIGHT(C5, LEN(C5)-3)

Here,

C5 = the cell to delete the letters from

  • Press Enter.

Remove First Letters from Cell with Formula in Excel

It will remove letters from the beginning of the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete all the letters from the start of the cells.

Formula Breakdown

  • LEN(C5) -> The LEN function defines the length of the Cell C5
    • Output: 6
  • LEN(C5)-3 -> becomes
    • 6-3
    • Output: 3
  • RIGHT(C5, LEN(C5)-3) -> becomes
    • RIGHT(C5, 3)
    • Output: 101
    • Explanation: Delete the first 3 letters from the Cell C5

5.2 Remove Last Letters from Cell with Formula in Excel

Steps to delete the last letters from cells with the formula in Excel are given below.

Steps:

  • First, select a cell that you want your result to be shown.
  • In the cell, write the following formula,
=LEFT(C5, LEN(C5)-2)

Here,

C5 = the cell to delete the letters from

  • Press Enter.

Remove Last Letters from Cell with Formula in Excel

It will remove letters from the end of the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete all the letters from the end of the cells.

Formula Breakdown

  • LEN(C5) -> Length of the Cell C5
    • Output: 6
  • LEN(C5)-2 -> becomes
    • 6-2
    • Output: 4
  • LEFT(C5, LEN(C5)-2) -> becomes
    • LEFT(C5, 2)
    • Output: WWE1
    • Explanation: Delete the last 2 letters from the Cell C5

6. Eliminate Both First and Last Letters from Cell with Formula in Excel

In this section, we will show you how to remove all the letters existing in a cell.

Steps to do that are given below.

Steps:

  • First, select a cell that you want your result to be shown.
  • In the cell, write the following formula,
=MID(C5,3,LEN(C5)-4)

Here,

C5 = the cell to delete the letters from

  • Press Enter.

Remove Both First and Last Letters from Cell with Formula in Excel

It will remove letters from both the start and the end of the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete all the letters from the beginning and the end of the cells.

Formula Breakdown

  • LEN(C5) -> Length of the Cell C5
    • Output: 6
  • LEN(C5)-4 -> becomes
    • 6-4
    • Output: 2
  • MID(C5,3,LEN(C5)-4) -> becomes
    • MID(C5,3,2)
    • Output: E1
    • Explanation: Delete the last 2 letters from the Cell C5 starting from the position of 3 with the MID function.

7. Delete Letters from Cell with Array Formula in Excel

If you work with a huge amount of data then you need a stronger way to eliminate all the letters. Implementing an Array formula to operate in a large amount of data is more quick and effective.

Here we will show you the Array formula to delete letters from cells in Excel.

Steps:

  • First, select a cell that you want your result to be shown.
  • In the cell, write the following formula,
=SUM(MID(0&C5,LARGE(INDEX(ISNUMBER(--MID(C5,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Here,

C5 = the cell to delete the letters from

  • Press Enter.

Remove Letters from Cell with Array Formula in Excel

It will remove all the letters from the cells in Excel.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete all the letters from the dataset of cells in Excel.

Notes:

  • This Array formula will remove all kinds of characters including letters, special characters etc. except the numeric characters. For example, if the original string is abc*123-def, this formula will remove all letters and special characters except numbers and return 123.
  • If there is no numeric character in the original string then this formula will return 0.

8. Eliminate First or Last Letters from Cell with User-Defined Function (UDF) in VBA

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this section, we will learn how to utilize VBA to delete letters from cells with a user-defined function(UDF) in Excel.

8.1 Delete First Letters from Cell with VBA in Excel

Steps to delete first letters from cells with VBA UDF in Excel are shown below.

Steps:

  • Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • In the pop-up code window, from the menu bar, click Insert -> Module.

  • Copy the following code and paste it into the code window.
Public Function DeleteFirstL(Irng As String, Icnt As Long)
DeleteFirstL = Right(Irng, Len(Irng) - Icnt)
End Function

This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF). So, after writing the code, instead of clicking the Run button from the menu bar, click Save.

Remove First Letters from Cell with VBA in Excel

  • Now go back to the worksheet of interest and write the function you just created with VBA code (Function DeleteFirstL in the first line of the code) and inside the parentheses of the DeleteFirstL function, pass the cell reference number that you want to remove letters from (in our case, we pass Cell B5 inside the parentheses) and the amount numbers that you want the letter to be stripped (we want first 3 letters to be removed so we put 3).
  • Press Enter.

Remove First Letters from Cell with VBA UDF in Excel

It will remove letters from the beginning of the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete 3 letters from the start of the cells.

8.2 Remove Last Letter from Cell with VBA in Excel

Steps to delete last letters from cells with VBA UDF in Excel are shown below.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Public Function DeleteLastL(Irng As String, Icnt As Long)
DeleteLastL = Left(Irng, Len(Irng) - Icnt)
End Function

Remove Last Letter from Cell with VBA in Excel

  • Save the code and go back to the worksheet of interest and write the function you just created with VBA code (Function DeleteLastL in the first line of the code) and inside the parentheses of the DeleteLastL function, pass the cell reference number that you want to remove letters from (in our case, we pass Cell B5 inside the parentheses) and the amount of numbers that you want the letter to be stripped (we want last 2 letters to be removed so we put 2).
  • Press Enter.

Remove Last Letter from Cell with VBA UDF in Excel

It will remove letters from the end of the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

It will delete the last 2 letters from the end of the cells.

8.3 Delete All Letters from Cell with VBA in Excel

Now we will learn how to delete all letters from cells with VBA UDF in Excel.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Function DeleteLetter(iTxt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
DeleteLetter = .Replace(iTxt, "")
End With
End Function

Remove All Letters from Cell with VBA in Excel

  • Save the code and go back to the worksheet of interest and write the function you just created with VBA code (Function DeleteLetter in the first line of the code) and inside the parentheses of the DeleteLetter function, pass the cell reference number that you want to remove letters from (in our case, we pass Cell B5 inside the parentheses).
  • Press Enter.

Remove All Letters from Cell with VBA UDF in Excel

It will remove all letters from the cell.

  • Drag the row down using Fill Handle to apply the formula to the rest of the cells.

You will get a dataset of cells stripped out of all the letters.


9. Delete Letters from Cell with Text to Column Tool of Excel

Excel has a built-in command tool called Text to Columns. We can utilize this tool to remove letters from cells in Excel.

Steps to do that are given below.

Steps:

  • Select the cells that you want to remove letters from.
  • Go to tab Data -> Text to Columns

Remove Letters from Cell with Text to Column Tool of Excel

  • From the pop-up window, select Fixed width as the data type.
  • Click Next.

  • Next in the Data preview, drag the vertical line until you reach all the letters that you want to remove (we want to remove WWE so we dragged the line covering all the WWE of the data value).
  • Click Next.

  • Pick the Column data format according to your need.
  • Click Finish.

You will get all the data except letters in another column.

Result of Remove Letters from Cell with Text to Column Tool of Excel

In this way, you can extract the letters that you want to remove from the cells.


10. Remove Letters from Cell using Flash Fill in Excel

You can also delete letters from cells using Excel’s Flash Fill feature. What Flash Fill does is, first it searches for any pattern that is provided by the user and then according to that pattern, it fills the other cells.

Steps to remove letters from cells using Flash Fill are given below. We will describe with an example to make you understand better.

Steps:

  • Look at the following picture, where we want to remove all the WWE from the Code WWE101. So the cell next to it, we wrote only 101 to familiarize Excel about the pattern that we want.
  • Then selecting the rest of the cells, we click Data -> Flash Fill.

Remove Letters from Cell using Flash Fill in Excel

It will fill all the rest of the cells with the same pattern that we provided, stripping out the WWE and leaving you only with the numbers.

Result of Remove Letters from Cell using Flash Fill in Excel

You can also press Keyboard Shortcut Ctrl + E to activate the Flash Fill.


Conclusion

This article showed you how to remove letters from a cell in Excel in 10 different ways. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


You May Also Like To Explore

Sanjida Ahmed

Hello, this is Sanjida, an Engineer who loves Sports a lot. Here I try to solve Excel problems with you. Hope I could be of great assistance.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo