Microsoft Office Tutorials and References
In Depth Information
Tip 55: Fixing Trailing Minus Signs
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 doesn’t convert these
values. In fact, it considers them to be non-numeric text.
The solution is so simple it may 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 55-1, 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. See Tip 64 for information about the Flash Fill feature.
Figure 55-1: The Trailing Minus for Negative Numbers option makes it very easy to fix trailing minus signs in a
range of data.