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.
You can also use “Text To Columns” to do this. Select the entire column, call up the “Text To Columns” dialog box (“Data” tab, “Data Tools” panel) and click the “Finish” button as soon as the dialog box appears. That’s it… you values are now normal numbers.
That is a useful trick; I was not aware of that. Thanks, Rick.