A very common problem is how to remove numbers from text cells in Excel. For some reason various systems mix up text and numbers purposefully e.g. Dixon (35208) or accidentally e.g. Ad8am Baum. Either way it should be, and is easy, to fix this in Excel.
Table of contents
YouTube Remove numbers from text cells
Find/ Replace with Wildcards
One way is to use the FIND/ REPLACE tool with wildcards. Wildcards are a way of telling Excel to find something e.g. ‘(‘ and ‘)’ but not to look at what is in between because this changes each time.
The Wildcard character * means unknown number of characters and ? represents a single character. So
- (*) would find (1) and (321554)
- whereas (?) would only find (1).
Below we want to remove the numbers in the brackets so that we are left with the name only. We need to tell Excel to remove the brackets and anything between the brackets (doesn’t matter how many characters).
To do this we
- highlight the column,
- Click CTRL + H (to bring up the find replace tool)
- In the Find What box type (*)- this tells Excel if must look for a ‘(‘, then other characters (as many as there are), and then a ‘)’.
- The Replace with box is left blank
- Then click Replace All
The end result will be that all the brackets with numbers in will be removed as show below. You may have noticed above that there looked like there was a space between the name and the brackets. If we also wanted to remove this space you would add the space in front of the ‘Find What’ option.
Use Flash Fill
The above is great where there is some form of pattern. In the case above the numbers were nicely packaged at the end between the brackets. What if the numbers are just randomly spread within the text?
Below we have numbers mixed in with the names e.g. Ad8am Baum instead of Adam Baum. All we need to do is type what we want to see in B2, highlight the appropriate cells, and click on Flash Fill (or CTRL + E)
In this case you will see that it gets the first one right and then the rest are not great. This is because we only gave it one example.
All we need to do is go to the first one that is wrong and type what we want to see. So AlBi6no must be changed to Al Bino. When you click enter, Excel will apply the 2 examples you have given it to everything (above and below)
Now you will see that all the numbers have been removed from the text.
Flash Fill would also work on the first example we gave with the numbers at the end.
Related
New Tool to clean data in excel 2013