Tip 55: 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...