Fix Numbers With Trailing Minus Signs

Do you sometimes need to fix data that has numbers, with trailing minus signs? I don’t run into this problem too often, but I usually fix them with the Text to Columns feature.

  • Select the column that contains the trailing minus signs
  • On the Excel Ribbon’s Data tab, click Text to Columns.

trailingminus01

  • Click the Next button in Steps 1 and 2.
  • In Step 3, click the Advanced button.

trailingminus02

  • Add a check mark to the option, “Trailing Minus for Negative Numbers”

trailingminus03

  • Click OK, and then click Finish.

The minus signs are magically moved to the front of the numbers, and they’ll calculate correctly if you include them in formulas.
trailingminus04

Fix Trailing Minus Signs With a Formula

If you need to fix trailing minus signs frequently, you might prefer a formula solution. With a formula, the numbers are fixed without your manual intervention, as soon as the data is pasted into the worksheet.
Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.

  1. In this example, the first number with a trailing minus sign is in cell A1
  2. Select cell B1, and enter this formula:
  3. =IF(RIGHT(A1,1)=”-“,-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))
  4. Copy the formula down to the last row of data.

In the formula, the RIGHT function returns the last character in cell A1.
If that character is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.
The minus sign before the VALUE function changes the value to a negative amount.

How Do You Fix Trailing Minus Signs?

Do you use another solution for trailing minus signs? There is a Trailing Minus Signs macro on the Contextures website, if you want to automate the fix.