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

While working with unstructured data, you might wish to thoroughly eradicate irrelevant texts or letters from cells in your dataset. Using Excel functions and features, we can delete these unnecessary letters easily. In this article, you will learn 10 suitable methods to remove letters from a cell in Excel. Before jumping into the methods, take a good look at the below overview of this article.

Remove Letters from Cell in Excel: 10 Methods

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 Find and Replace Feature in Excel

The Find & Replace command is the easiest and the most common feature to do most 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.

Steps:

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

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

• Press Replace All.

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 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,

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. 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,

Here,

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

• Press Enter.

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.

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

3. Extract Letters from 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,

Becomes,

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.

• 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 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,

Where,

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

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,

• 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 the 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,

Here,

C5 = the cell to delete the letters from

• Press Enter.

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.

5.2 Remove Last Letters from Cell with Formula in Excel

Steps to delete the last letters from cells with the combined formula of LEFT and LEN functions 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,

Here,

C5 = the cell to delete the letters from

• Press Enter.

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.

Read More: How to Remove Text After Character in Excel

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 by merging the MID and LEN functions.

Steps:

• First, select a cell that you want your result to be shown.
• In the cell, write the following formula,

Here,

C5 = the cell to delete the letters from

• Press Enter.

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.

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 quicker and more 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,

Here,

C5 = the cell to delete the letters from

• Press Enter.

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.

Read More: How to Remove Text from an Excel Cell but Leave Numbers

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 the 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.

• 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.

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 the 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

• 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.

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

• 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) 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.

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.

Read More: How to Remove Text before a Space with Excel Formula

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:

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

• 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 needs.
• Click Finish.

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

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 with the pattern that we wanted.
• Then selecting the rest of the cells, we click Data -> Flash Fill.

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.

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

Read More: How to Remove Specific Text from Cell in Excel

Download Workbook

You can download the free practice Excel workbook from here.

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.

Related Articles

• How to Remove Text between Two Characters in Excel
• How to Remove Everything After a Character in Excel