7 Ways to Remove Letters from Cell in Excel

In Excel, we can easily remove letters from a cell. Using different functions, formulas, and built-in features, we can remove letters from the text’s start, end, or middle. So, we will demonstrate 7 diverse ways to remove letters from cell in Excel.


📁 Download Excel File

Download the Excel file used for the demonstration from the link below.


Learn to Remove Letters from Cell in Excel with These 7 Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the product code numbers: the first two letters are product initials, and the numbers are serial numbers. Now, we want the product initials removed from the code and want to know the serial numbers. Hence, we will use this dataset to demonstrate 7 simple methods to remove letters in cell in Excel.

Approach 1

1. Using Find and Replace Feature

In this first method, we used Find and Replace feature to remove letters from a cell in Excel. Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, copy cell B6:B9 by pressing Ctrl+C and paste them by pressing Ctrl+V in cell C6.

  • Now select the pasted cells C6:C9 and press Ctrl+H.
  • A dialog box named Find and Replace will appear, and select Replace from there. To extract the username, type PR into Find what, keep Replace with option blank and click on Replace All.

Using Find and Replace to remove letters from cell in excel

  • Hence, we will have the product initials removed.

Approach 2

2. Applying SUBSTITUTE Function

In this second method, we applied the SUBSTITUTE function to remove letters from cell from different places of the text string in Excel.

SUBSTITUTE Function

2.1 Simple SUBSTITUTE Formula

In this method, we used a simple formula containing SUBSTITUTE function only to remove specific letters from a cell in Excel. The SUBSTITUTE function usually swaps new text with old text. So, let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the desired result. Here, we are selecting cell C6.
  • Next, insert the formula.

=SUBSTITUTE(B6,”PR”,””)

Using SUBSTITUTE function to remove letters from cell in excel

Here, cell B6 contains the text string, and “PR” is the old text we want to remove, so we replaced it with a blank.

  • After that, select cell C6 again and drag the Fill Handle down, or double-click on the plus that appeared after selecting and AutoFill the cells. Finally, the outcome is as follows.

SUBSTITUTE Function

2.2 Substituting Nth Letter

In this method, we again used SUBSTITUTE function to remove N-th letters from a cell in Excel. The SUBSTITUTE function usually swaps an old text with a new text. So, let us see the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell C6 and insert the formula.

=SUBSTITUTE(B6,”PR”,””,1)

Using SUBSTITUTE function to remove Nth letters from cell in excel

Here, cell B6 contains the text string PPR03-116-001, and “PR” is the old text we want to remove, so we replaced it with a blank. In addition, the number “1” represents that we want to replace first “P” only.

  • After that, select cell C6 again and drag the Fill Handle down, or double-click on the plus that appeared after selecting and AutoFill the cells. Finally, the outcome is as follows.

SUBSTITUTE Function

2.3 Nested SUBSTITUTE

In this method, we used SUBSTITUTE function to remove letters from a cell in Excel. However, this time we used it nested in the formula. You can use this formula if you want to remove different parts of the text string. Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell C6 and insert the formula.

=SUBSTITUTE(SUBSTITUTE(B6,”P”,””),”R”,””)

Using nested SUBSTITUTE function to remove letters from cell in excel

Here, cell B6 contains the text string PR03-116-001, and “P” is the first old text we want to remove, so we replaced it with a blank. Then the nested SUBSTITUTE function swaps “R” with blank.

  • After that, select cell C6 again and drag the Fill Handle Finally, the outcome is as follows.

Approach 3

3. Removing Letters Combining RIGHT, LEFT, MID and LEN Functions

In this method, we employed RIGHT, LEFT, MID and LEN functions to remove letters from the start end and middle position of text strings.

Removing Letters

3.1 Removing Letters Combining RIGHT and LEN Functions

In this method, we will remove the first two letters of text using RIGHT and LEN functions. The RIGHT function extracts from the right side of a text string according to the specified characters, and LEN function returns the number of characters in a text string. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, insert the formula in cell C6.

=RIGHT(B6,LEN(B6)-2)

Applying RIGHT and LEN functions to remove letters from cell in excel

🔨 Formula Breakdown

Here, the formula we inserted in cell C6 is:

RIGHT(B6,LEN(B6)-2)

👉 In this function, LEN(B6) looks into cell B6 and returns the number of characters in that cell which is 12.

👉 Then, LEN(B6)-2 subtracts 2 from the value returned by LEN function.

👉 Lastly, the RIGHT function returns right most 10 characters and the first two letter from the start gets removed.

  • After that, select cell C6 again and drag the Fill Handle Finally, the outcome is as follows.

Removing Letters

3.2 Removing Letters Combining LEFT and LEN Functions

In this method,we will remove a particular length from the end of a text. We have used LEFT and LEN functions to do this. The LEFT function usually returns the first character or characters in the text string from the left side of the defined cell, depending on the number of characters you choose. On the other hand, LEN function returns the number of characters in a text string. Now let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell C6 and insert the formula.

=LEFT(B6,LEN(B6)-1)

Applying LEFT and LEN functions to remove letters from cell in excel

🔨 Formula Breakdown

Here, the formula we inserted in cell C6 is:

LEFT(B6,LEN(B6)-1)

👉 In this function, LEN(B6) looks into cell B6 and returns the number of characters in that cell which is 12.

👉 Then, LEN(B6)-1 subtracts 1 from the value returned by LEN function.

👉 Lastly, the LEFT function returns left most 11 characters and the last letter from the end gets removed.

  • After that, select cell C6 again and drag the Fill Handle Finally, the outcome is as follows.

Removing Letters

3.3 Removing Letters Combining MID and LEN Functions

In this method, we will remove first two letters of text using MID and LEN functions. The MID function usually returns a particular number of characters from the text string depending on the number of characters you choose., and LEN function returns the number of characters in a text string. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, insert the formula in cell C6.

=MID(B6,3,LEN(B6)-2)

Applying MID and LEN functions to remove letters from cell in excel

🔨 Formula Breakdown

Here, the formula we inserted in cell C6 is:

MID(B6,3,LEN(B6)-2)

👉 In this function, LEN(B6) looks into cell B6 and returns the number of characters in that cell which is 12.

👉 Then, LEN(B6)-2 subtracts 2 from the value returned by LEN function.

👉 Lastly, the MID function returns 10 characters starting from the third character and the first two letter from the start gets removed.

  • After that, select cell C6 again and drag the Fill Handle Finally, the outcome is as follows.

Approach 4

4. Employing Array Formula

This method will employ an array formula consisting of SUM, MID, LARGE, INDEX, ISNUMBER, and ROW functions to remove all letters and characters from a text string and keep only numbers.

Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • Insert the formula in cell C6.

=SUM(MID(0&B6,LARGE(INDEX(ISNUMBER(-MID(B6,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

Applying an array formula to remove letters from cell in excel

🔨 Formula Breakdown

Here, the formula we inserted in cell C6 is:

SUM(MID(0&B6,LARGE(INDEX(ISNUMBER(-MID(B6,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

👉  In this function, ROW($1:$99) returns number 1 to 99.

👉 Then, MID(B6,ROW($1:$99),1) part returns one character starting from characters in the text string of the row number returned from this function ROW($1:$99).

👉 ISNUMBER(-MID(B6,ROW($1:$99),1)), part returns TRUE or FALSE depending on the value returned from MID function. It returns TRUE  if there is any number and otherwise FALSE.

👉 INDEX(ISNUMBER(-MID(B6,ROW($1:$99),1))*ROW($1:$99),) returns the row number of the cells if ISNUMBER function returns TRUE.

👉 LARGE function returns the k-th largest value from an array. LARGE(INDEX(ISNUMBER(-MID(B6,ROW($1:$99),1))*ROW($1:$99),)

Here,k is the output from this ROW($1:$99), and the output from INDEX function represents the array.

👉 Then, in MID function 0&B6 refers to the text, output from LARGE function refers to the starting number and 1 is the number of characters to return.

👉 10^ROW($1:$99)/10 returns the multiples of 10 according to the row number.

👉 Lastly, the SUM function adds up the output from MID function and multiples of 10 and thus we get all the numbers extracted and letters removed.

  • Lastly, select cell C6 again and drag the Fill Handle down; the outcome is as follows.

Approach 5

5. Utilizing Text to Columns

Using Excel’s Text to Columns feature, we can remove letters from cell. Therefore, we will show how to use this feature to remove letters  from a cell in Excel. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cells B6:B9.
  • After that, go to the Data tab in Ribbon. From the Data Tools, select Text to Columns.

Applying Text to Columns to remove letters from cell in excel

  • Then, a box named Convert Text to Columns Wizard will appear. Select Fixed width from the original data type and press Next.

  • Next, select create break line after “PR” by clicking to signify a column break and click on Next.

  • Then, type $C$6 in the destination option and click on Finish.

  • Finally, we will have Product Initials and Serial Numbers extracted.

Approach 6

6. Applying Flash Fill

In this method, we will apply Flash Fill to remove letters from cell in Excel. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, type the letters you want to keep in the desired cell. We typed 03-116-001 into cell C6.

  • After that, drag the Fill Handle down and select Flash Fill from Auto Fill Options, and we will have removed the letters from the cells.

Applying Flash Fill to remove letters from cell in excel

Approach 7

7. Embedding VBA

This method will demonstrate how to remove letters in a cell in excel using Microsoft Visual Basic Application (VBA). This VBA code extracts texts from sentences or a group of words. Hence, we used a new data set containing sentences.

Also, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

Embedding VBA

7.1 Removing Letter from Start

This VBA code removes letters from the start of the text string. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, go to the Developer tab and select Visual Basic from the code group.

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

  • Enter the code and save it.
Public Function RemFLet(InpStr As String, LetCnt As Long)

RemFLet = Right(InpStr, Len(InpStr) - LetCnt)

End Function
  • Now, to run the code, select cell C6 and insert the formula.

=RemFLet(B6,2)

Embedding VBA to remove start letters from cell in excel

Here, cell B6 contains the text, and 2 is the number of characters we want to remove from the start of the text string.

  • Lastly, drag the Fill Handle and AutoFill the rest of the cells, and we will have our desired output.

Embedding VBA

7.2 Removing Letter from End

This VBA code removes letters from the end of the text string. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, go to the Developer tab and select Visual Basic from the code group.

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

  • Enter the code and save it.
Public Function RemLLet(InpStr As String, LetCnt As Long)

RemLLet = Left(InpStr, Len(InpStr) - LetCnt)

End Function
  • To run the code, select cell C6 and insert the formula.

=RemLLet(B6,1)

Embedding VBA to remove end letters from cell in excel

Here, cell B6 contains the text, and 1 is the number of characters that we want to remove from the end of the text string.

  • Lastly, drag the Fill Handle and AutoFill the rest of the cells, and we will have our desired output.

Embedding VBA

7.3 Removing All Letters

This VBA code removes all letters from the text string. Now let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Developer tab and select Visual Basic from the code group.

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

  • After that, enter the code and save it.
Function RemALet(InpTxt As String) As String

With CreateObject("VBScript.RegExp")

.Global = True

.Pattern = "\D"

RemALet = .Replace(InpTxt, "")

End With

End Function
  • To run the code, select cell C6 and insert the formula.

=RemALet(B6)

Embedding VBA to remove all letters from cell in excel

Here, cell B6 contains the text, and the function removes all letters from the text string.

  • Lastly, drag the Fill Handle and AutoFill the rest of the cells, and we will have our desired output.


📝 Takeaways from This Article

📌  This article demonstrated 7 methods to remove letters from a cell in Excel.

📌  In the first method, we used Find and Replace to remove letters.

📌  Then, we demonstrated three different formulas with SUBSTITUTE function to remove letters.

📌  Next, we combined LEFT, RIGHT, and MID functions to remove letters as general characters from the text string’s start, end, and middle positions in Excel.

📌  After that, we employed an array formula to remove letters from a cell in Excel.

📌  Then, we utilized Text to Column to remove letters.

📌  Next, we applied Flash Fill to remove letters.

📌  Finally, we employed three different VBA codes to remove letters as general characters from the start, end, and middle positions of a text string in Excel.


Conclusion

This article has demonstrated 7 methods to remove letters from a cell in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

(Visited 52 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo