Join Dennis Taylor for an in-depth discussion in this video Dealing with special characters and using wildcards, part of Cleaning Up Your Excel 2010 Data.
In addition to standard kinds of replacement techniques available with Find and…Replace, you may need to have to deal with special characters, like the asterisks…that we see in Column D. And there is also another function, one called…Substitute, that in many ways solves problems that Find and Replace can't handle.…Let's deal, first of all, with the issue in Column D. Who knows how those…asterisks got in there?…We simply want to get rid of them,…or possibly substitute some other character for them.…Let's click Column D. And using Find & Select, the rightmost button on the…Home tab in the Ribbon,…let's go to Replace, and we would like to replace that asterisk--…that's all we want to replace.…
We don't want to match the entire cell;…we simply want to replace the asterisk with, how about nothing? So this is empty.…Replace All. 742 replacements, but look at Column D. It's all gone.…So how do we deal with that?…What we've got to do for now is click OK, close, and undo.…I'll press Ctrl+Z here.…The asterisk is a special wildcard character.…
- Moving or inserting rows and columns of data with a simple drag
- Using Text to Columns
- Harnessing the Find and Replace command to replace data at the character level
- Dealing with special characters and wildcards during search
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.