Microsoft Office Tutorials and References
In Depth Information
This formula inserts a hyphen after the third character in a cell:
You can also use Flash Fill to add text to cells.
Fixing trailing minus signs
Imported data sometimes displays negative values with a trailing minus sign. For example, a negative value
may appear as 3,498- rather than the more common -3,498 . Excel does not convert these values. In fact, it con-
siders them to be non-numeric text.
The solution is so simple it may even surprise you:
1. Select the data that has the trailing minus signs. The selection can also include positive values.
2. Choose Data ⇒ Data Tools ⇒ Text to Columns.
3. When the Text to Columns dialog box appears, click Finish.
This procedure works because of a default setting in the Advanced Text Import Settings dialog box (which you
don't even see, normally). To display this dialog box, shown in Figure 16-23, go to Step 3 in the Text to
Columns Wizard dialog box and click Advanced.
Or, you can use Flash Fill to fix the trailing minus signs. If the range contains any positive values, you may
need to provide several examples.
Figure 16-23: The Trailing Minus for Negative Numbers option makes it very easy to fix trailing minus signs in
a range of data.
A Data Cleaning Checklist
This section contains a list of item that could cause problems with data. Not all these are relevant to every set of
• Does each column have a unique and descriptive header?
• Is each column of data formatted consistently?