Microsoft Office Tutorials and References
In Depth Information
they occur within your data. For example, if you tabulate data by month using a pivot table, a misspelled month
name will make it appear that a year has 13 months.
To access the Excel spell checker, choose Review ⇒ Proofing ⇒ Spelling or press F7. To check the spelling in just
a particular range, select the range before you activate the spell checker.
If the spell checker finds any words that it does not recognize as correct, it displays the Spelling dialog box. The
options therein are fairly self-explanatory.
Replacing or removing text in cells
You may need to systematically replace (or remove) certain characters in a column of data. For example, you
may need to replace all backslash characters with forward slash characters. In many cases, you can use Excel's
Find and Replace dialog box to accomplish this task. To remove text using the Find and Replace dialog box,
just leave the Replace With field empty.
In other situations, you may need a formula-based solution. Consider the data shown in Figure 16-22: The goal
is to replace the second hyphen character with a colon. Using Find and Replace wouldn't work because there's
no way to specify that only the second hyphen should be replaced.
Figure 16-22: To replace only the second hyphen in these cells, Find and Replace is not an option.
In this case, the solution is a fairly simple formula that replaces the second occurrence of a hyphen with a colon:
To remove the second occurrence of a hyphen, just omit the third argument for the SUBSTITUTE function:
This is another example in which Flash Fill can also do the job.