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.
Leave a Reply