Three Ways to remove unwanted characters from Excel

3 Ways to remove unwanted characters from Excel

Spread the love

After completing a document or any work, we may find many unwanted or unexpected characters in the Excel sheet. It’s somewhat difficult to erase all of them at a time.

You can Remove Characters apparatus from Text Toolkit for Excel causes you to expel custom characters and character sets in Excel by position or erase every one of their events in the chose cells. Effectively clear all letters, digits, non-printing characters, and accentuation marks. It’s additionally conceivable to enter and expel a substring from your range.

Before starting, better have a copy of the document because in case the file is unexpectedly deleted or corrupted, you may lose data. Always keep a copy of the important documents.

How to remove unwanted/useless characters in Excel?

Steps are below,

Method 1:-

Step 1): At first,  open the Microsoft Excel document in which you want to completely delete the useless characters.

Step 2):  Next, you can select the “ Data “ option which is besides “ Tools “ at the top of the screen.

remove character option in excel

Step 3):  Afterwards, you can see some options. Then choose “ Remove “. You will get two options tilted Remove Characters and Remove by position.

Step 4): Select the “ Remove Characters “ option which is second. Then, it will display an options menu as below.

remove numeric character sets

Step 5):  Moreover, select the cells in which you want to delete the characters and click on the selection icon which is at right to the box looking like a square.

Step 6): Evacuate custom characters will erase the characters you go into this field. On the off chance that you have a few images that you have to remove from your range and you would prefer not to rehash this normal activity, simply enter each character to take out into the Remove custom characters field and the include will erase every one of its cases in the chose cells.

Note :

  • Expel character sets. There are a few arrangements of images you can pick starting from the drop list:
  • Non-printing characters – erase all non-printing characters like line breaks, the initial 32 non-printing characters in the 7-piece ASCII code (values 0 through 31), and extra non-printing characters (values 127, 129, 141, 143, 144, and 157).
  • Content characters – expel all letters from your cells.
  • Numeric characters – erase all digits from the scope of intrigue.
  • Images and accentuation marks – expel all accentuation marks from the cells just as the accompanying images: scientific, geometric, specialized and cash images, letter-like images, for example, ? 1, and “.
  • Evacuate a substring. Erase any mix of characters, for example, a word, from the chose cells.

Note 2 :

  • Be cautious with the content case: the substring will be erased just in the event that it has a similar bookcase as the worth you enter.
  • That’s it, click on finish to see the results out.
  • Identically we can also remove the characters by position.

Let’s see how in the below steps,

** Method 2 – Removing characters by position.

Step 1):  In the same fashion, open excel and after open the appropriate document in which you want to remove/delete the unwanted characters.

Step 2):  Then select the “ Data “ option under the “ Tools “ at the top of the screen.

remove character option in excel

Step 3):  Furthermore you can see some options. Then, Choose “ Remove “. You will get two options tilted Remove Characters and Remove by position.

Step 4): Therefore select the “ Remove Characters “ option which is second. Then, it will display the option menu below.

remove numeric character sets

Step 5):  Select the cells in which you want to delete the characters and click on the selection icon which is at right to the box looking like a square.

Step 6): You can select the radio tab along with the option which option you think is better for your document.

Now, Delete any range of characters at the start of the required cells in surpass.

Step 7): The last N characters. take away any range of characters at the top of every cell in your vary.

Step 8): All characters before the text. Delete any values before the desired character or string within the vary you choose.

Step 9): All characters when text. take away everything when the desired character or string within the elect cells.

Now, just click on remove to get the results.

*** Method 3 – By using Flash Fill to remove the first character

Here are the steps for that process.

First of all, here is a note before using this process.

Note :

Of course, this option is only active on Excel versions from 2013 and above. If you have any older version before 2013, try to download the latest one.

Step 1): Firstly, open the excel file in which you want to make changes in characters .

Step 2):  Then create a cell which should be adjacent to the data cell as below.

Original dataNew data
GJ69876987
GJ66896689
GJ65786578
GJ68486848

Notably, If you write like this, the first characters “GJ “ will be deleted and the values will be separated.

In case, if you want to remove more than two characters, then just create a new adjacent cell and then write the remaining data which is not going to be deleted in the new cell.

This means, the data which you left in the old cell will be removed.

While doing this process, after selecting the first cell then you can auto-select the remaining cells by the “Autofill icons” option. Means if you select “GJ “ in the first cell, and click on the Autofill then the same “ GJ “ in remaining all cells will be deleted automatically.

Step 3) : And finally, click on the last option “ Flash Fill “ to get the desired output you want.

The below image shows it ,

That’s it.. You will get the results what you required…

flash fill option in excel sheet

See Also…

Learn about how to create a graph in excel document

Leave a Comment

Your email address will not be published. Required fields are marked *

Do NOT follow this link or you will be banned from the site!