How to remove characters in Excel

By |2018-05-18T05:43:12+00:00May 18th, 2018|

The aim of this tutorials is to show you the shortest possible ways to remove certain characters in Excel. Maybe there are certain characters or letters you want to delete from your cells leaving the remaining content. This tutorial is created just for that.

Let me save some time by illustrating with this clear example. In the picture below is a list of female names with the title Mrs. attached to each name. We don’t want to see these titles anywhere near the names. So we are going to find ways and means to trim the Mrs. part leaving the names only in the shortest possible way.

remove characters in Excel

FIRST METHOD: Using Find and Replace

The easiest way possible to remove certain characters from Excel is to use the Find and Replace command. These characters you want to remove could be anywhere in the middle of the text, beginning or at the end. The most important thing is that we want to get rid of them.

And with this find and replace method, we open up the Find and Replace dialogue box, type what we want to replace in the Find what field and leave the Replace with field empty.

Now considering the example above, we want to remove the Mrs. part of the text. The steps below show how to do just that.

STEP #ONE: Press Ctrl + H

This is the shortcut to display the Find and Replace dialogue box. After pressing this key combination the Find and Replace dialogue box will appear.

remove characters in Excel

STEP #2: Type Mrs. in the Find what field and leave the Replace What blank.

That’s all there is to it. Now click the Replace All button and the first four characters (Mrs.) will be deleted completely from all the cells.

remove characters in Excel

NOTE: The Find and Replace method is for you only if the characters you want to trim out is not part or needed in any other cells. Because with this method, Excel is going to look into every corner of your worksheet and delete any cell that contains that string. For instance in our example, if we want to get rid of the title (Mrs.) from the list in Column A without touching any other Mrs. characters in the worksheet, then we cannot use the Find and Replace function for this job.

remove characters in Excel

SECOND METHOD: Using the RIGHT () or LEFT() function

Now that we’ve found success with our first and shortest method, let’s dive into some of the other available options. The first option being the shortest does not mean it will solve all of our problems concerning trimming out text either from the left, right or middle. This second method is suitable for cases where we want our trimming to apply only to certain cells and not the entire worksheet.

remove characters in Excel

For instance this time around, our example is going to be like this: We have this list in both column A and column B which both contains the title (Mrs.). Our job is to remove the first words (Mrs.) in column B without touching column A.

We can achieve this result using the RIGHT function. You may be wondering why the right function and not the left function. It is pretty simple here: if what you want to trim out is at the other left side of your screen then you have to use the Left () function. You’ll understand things better after when we are done with this example.

Step #one: Type this function in cell C1: =RIGHT (B1,LEN(B1)-5)

remove characters in Excel

Step #two: Press Enter to insert the function

remove characters in Excel

Step #three: DoubleClick the Fill handle


remove characters in Excel

Step #four: Copy Column C and past as values in column B

Now that we have our results, the next step is to arrange our worksheet to look normal. In this example, we want to copy the trimmed list in column C into Column B, and then delete the C column. This is very straight forward: just copy the whole of column C and past as values in column B, then delete the C column – plain and simple.

Thanks very much for reading. If you happen to have a special problem please don’t hesitate to let me know in the comments below.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I Know You Better!
Subscribe To My Newsletter
Be the first to get latest Excel updates and
exclusive content straight to your email inbox.
Yes, I want to receive updates
No Thanks! I Don't Want to Learn Excel.

Not Sure What to Learn in Excel?

Sign Up For My Actionable Excel Techniques You Can Use Right Away!
Send me Updates
You'll Get One Email Every Week!