As an Excel expert, I often get asked about removing unwanted characters from spreadsheets. However, before I can provide a solution, I need to ask a few questions to determine the reason for removal and the desired outcome:
- What was the actual reason for calling them unnecessary/unwanted and getting rid of them?
- What exactly does the deletion procedure imply? Will we necessarily delete them, or will we replace them with some other characters, or maybe move them to another column?
- Does it really make sense to delete the characters themselves? Or maybe, we should remove the words that contain them from the text? Or even the entire contents of the cells?
- Instead of deleting these characters, wouldn’t it be easier to consider an operation such as extracting certain characters from the text besides those to be deleted?
Depending on the answers to these questions, there may be many different solutions. Somewhere you can do with simple functions, somewhere you can connect regular expressions, and somewhere you will need a ready-made software solution.
Delete characters by their type
One common task is removing characters by their type. Unfortunately, Excel does not provide an easy way to delete character sets by their attribute, and the only option is to use the “find and replace” function to delete one character or substring at a time. However, with some effort, we can achieve the desired result.
Delete everything except letters and numbers (remove punctuation)
Removing all characters except letters and numbers, or punctuation, can be a daunting task, as there are hundreds of such characters. Check out this article for a solution.
Obsolete spaces
Repeated spaces between words or spaces at the beginning and end of a cell can also be considered unnecessary. To remove them, follow the instructions in this article.
Unnecessary characters on the right / left
If unwanted characters are located on the right or left side of the text in a cell, we may want to cut them off. This can be done
- by position or
- by a specific border.
Learn how to, correspondingly
- remove first N characters (from left)
- or remove last N characters (from right)
If there is a boundary for deletion and it is a particular character or substring:
- this guide will help you remove everything in front of it.
- and this one will help you delete all after it.
Numbers
It happens that the unnecessary characters are digits, of which there are ten, and you want a quicker way than clearing the lines from them by replacing them with a blank. How to remove digits from the text in cells – this section will answer the question.
Letters, Latin chars
Similarly, it is difficult to remove all the letters of the alphabet at once, of which there are 26 English and way more diacritic characters.
To learn how to delete Latin characters in Excel, see my guidebook.
Delete everything except…
It is often the case that all but certain characters are considered unnecessary.
Delete all but the digits (extract the digits)
Phone numbers, postal codes, numeric articles, IP addresses… Sometimes the problem is the presence of characters other than numbers in the cells. Read about it: Delete everything but numbers in Excel cells.
Delete all but the letters (extract the letters)
The case when any punctuation, numbers and other characters other than letters of the alphabet are superfluous in the data. This could be Latin characters or any other letters.
Other operations with symbols in Excel
It is not always necessary to take such drastic measures as removing characters. Sometimes you just need to detect their presence, extract them, or replace them with some other symbols. The corresponding sections of the site will help you solve such problems:
- Find characters;
- Extract characters;
- Change characters.
Microsoft Excel is also able to use the full power of regular expressions. Letters, numbers, punctuation marks, special characters – regular expressions can handle any kind of data. Learn more in this article on regular expressions in Excel.