Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign, as seen in the following Before and After pictures.

Here are the steps to convert those wannabe negative numbers to real negative numbers.

Step 1
Using a helper column, enter the conversion formula as shown in cell E3 copied down as needed:
=IF(RIGHT(D3)="-",LEFT(D3,LEN(D3)-1)*-1,D3+0)

Step 2
• Select the range of conversion formula cells.
• From your keyboard press Ctrl+C to copy that range.

Step 3
• Select the range of original numbers needing to be converted and paste special for values.
If you are using Excel version 2003:
From your keyboard press Alt+E, then Enter, then S V, then Enter.
If you are using Excel version 2007 or later:
From your keyboard press Alt+E+S+V+Enter.

Step 4
• Press the ESC key on your keyboard to exit Copy Mode.
• Not pictured but a good idea, delete the helper formulas, in this example E3:E10.