5 Methods to Remove Characters from the Right in Excel

We can remove characters from the right in Excel 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.

Remove Characters from Right in Excel.xlsx

Table of Contents

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)

Formula

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

To remove a specified number of characters from the right in Excel:

Select cell C2.

Insert the formula: =LEFT(A2,LEN(A2)-B2). Here, cell B2 specifies the number of characters to remove.
Press ENTER.
Select the entire column.

Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.

Now see all the specified characters are removed from the right.

7+ Methods to Remove Characters from Left in Excel

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)

Formula

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

Follow these steps to remove all characters from the right after a specific character in Excel:

Select cell B2.
Insert the formula: =LEFT(A2, SEARCH(” “, A2) -1)

Press ENTER.
Select the entire column.
Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.

This formula removes everything after the first space from the right side of a string.

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.

Steps to remove characters from the right in Excel using the Find and Replace tool:

Select all the texts.

Press CTRL + H to open the Find and Replace dialog box.
Click on the Find what box.
Press the Space bar once to insert a space there.

Then insert an asterisk (*) after the space.
Leave the Replace with box blank.
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.

Click OK in the small pop-up dialog box.
Hit the Close button of the Find and Replace dialog box.

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

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.

To clear characters from the right in Excel using Flash Fill:

Create a blank column right next to the column having the texts to remove.
Type the texts without the characters from the right in the top two cells of the adjacent column.
Select the whole column.

Click on the Fill drop-down in the Home tab.
Click on Flash Fill.

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

5 Ways to Remove Numeric Characters from Cells in Excel

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.

Option Explicit
Function Clear_Char_from_Right(pStr As String, pChars As Long)
Clear_Char_from_Right = Left(pStr, Len(pStr) - pChars)
End Function

Syntax

=Clear_Char_from_Right(text, number_of_characters)

Formula

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

Now follow these steps to remove characters from the right using the custom formula:

Select cell C2.

Insert this formula: =Clear_Char_from_Right(A2,B2)
Press ENTER.
Select the entire column.

Press CTRL + D. This will copy down the formula applied in cell C2 with changing cell reference.

All the specified characters are removed from the right.

Conclusion

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

Frequently Asked Questions

How can I remove characters from the right in Excel?

You can remove characters from the right in Excel using the Flash Fill tool or a combination of functions like “LEN” and “LEFT.”

Can you provide an example of using the “LEFT” function to remove characters from the right?

Certainly! If you have a text string in cell A1, and you want to remove the last 3 characters, you can use the formula =LEFT(A1, LEN(A1)-3).

Can I remove a specific number of characters regardless of their position from the right?

Yes, you can. If you want to remove, for instance, the last 5 characters from any position in a text string in cell A1, you can use the formula =LEFT(A1, LEN(A1)-5).

Related Articles