5 Approaches to Remove Characters from the Right in Excel

We can remove characters from the right using either formulas, commands, or VBA scripts. I have discussed 5 approaches in total to remove characters from the right in Excel. Hope you will find your desired solution after reading this article.

A. Using Formulas

Case #1: Remove Specified Number of Characters from the Right in Excel

If you already know how many characters you want to remove from the right, you can use the LEFT and LEN functions to do that.

Syntax

=LEFT(text,LEN(text)-num_chars)

Usage Guide

Step_1: Insert the formula in cell C2.

=LEFT(A2,LEN(A2)-B2)

---

Formula Explanation

• A2 has the text (Andre Botha) from where we want to remove characters.
• B2 has the number of characters (5) to remove from cell A2. You can also directly specify the number in the formula.
• LEN(A2) determines the total number of characters in cell A2 which is 11.
• The above formula ultimately becomes LEFT(Andre Botha,11-5) e. LEFT(Andre Botha,6).
• The LEFT function returns the first 6 characters from the left. Thus, the 5 characters are removed from the right.

---

Step_2: Press ENTER.

Step_3: Select the entire column.

Step_4: Press CTRL + D.

This will copy down the formula applied in cell C2 with changing cell reference.

Final Result

All the specified characters are removed from the right.

Case #2:  Remove All Characters from the Right After a Specific Character in Excel

The formula that I used in this method can remove all characters from the right after a specific character.

Here, I will use the formula to delete everything after the first space encountered.

Syntax

=LEFT(text,SEARCH(find_text,within_text)-1)

Usage Guide

Step_1: Use the formula in cell B2.

=LEFT(A2, SEARCH(" ", A2) -1)

---

Formula Explanation

• A2 has the text (Andre Botha) from where we want to remove characters.
• SEARCH(” “, A2) returns the location of the first space (“”) encountered which is
• The LEFT function returns only the first 5 (6-1) characters from the left. So, everything after the first space encountered is deleted automatically.

---

Step_2: Press ENTER.

Step_3: Select the entire column.

Step_4: Press CTRL + D.

This will copy down the formula applied in cell C2 with changing cell reference.

Final Result

Everything after the first space is deleted from the right.

B. Without Using Formulas

Method #1: Remove Characters from the Right in Excel Using Find and Replace

Here, I will use the Find and Replace dialog box to remove all the characters from the right after a specific character.

In this case, the specific character is a space (“”). So, only the first name will be kept removing the last name.

Usage Guide

Step_1: Select all the texts.

Step_2: Press CTRL + H to open the Find and Replace dialog box.

Step_3: Click on the Find what box.

Step_4: Press the Space bar once to insert a space there.

Step_5: Then insert an asterisk (*) after the space.

Step_6: Leave the Replace with box blank.

Step_7: Hit the Replace All button.

This command will replace all the characters after the first space with null values.

After hitting the Replace All button, MS Excel will display a dialog box that says all replacement has been done.

Step_8: Click OK in the small pop-up dialog box.

Step_9: Hit the Close button of the Find and Replace dialog box.

Final Result

All the characters after the first space are replaced with null values. Thus, all the characters are removed from the right.

Method #2: Clear Characters from the Right in Excel Using Flash Fill

If your data has a pattern, then you can use the Flash Fill command to remove characters from the right.

Usage Guide

Step_1: Create a blank column right next to the column having the texts to remove.

Step_2: Type the texts without the characters from the right in the top two cells of the adjacent column.

Step_3: Select the whole column.

Step_4: Click on the Fill drop-down in the Home tab.

Step_5: Click on Flash Fill.

Final Result

Excel will automatically recognize the pattern and remove all the characters from the right after the first space.

Remove Characters from the Right in Excel with VBA Code

The following code creates a function named Clear_Char_from_Right.

You can use this customized function to remove characters from the right in Excel.

VBA Code

Option Explicit

Function Clear_Char_from_Right(pStr As String, pChars As Long)

Clear_Char_from_Right = Left(pStr, Len(pStr) - pChars)

End Function

Formula Syntax

=Clear_Char_from_Right(text, number_of_characters)

Usage Guide

Step_1: Insert the formula in cell C2.

=Clear_Char_from_Right(A2,B2)

---

Formula Explanation

• A2 has the text from where we want to remove the characters from the right.
• B2 has the number of characters to remove from the right.

---

Step_2: Press ENTER.

Step_3: Select the entire column.

Step_4: Press CTRL + D.

This will copy down the formula applied in cell C2 with changing cell reference.

Final Result

All the specified characters are removed from the right.

Conclusion

I have discussed 5 different approaches to removing characters from the right. I hope, you’ve found your required solution from this blog.