Save 5 minutes a day by using ASAP Utilities to quickly fix numbers with a trailing minus sign.
"How can I flip the minus sign and make it a real negative number?"
We were recently asked:
"I have downloaded a report that shows negative numbers as follows: 123.25-
How can I make the negative symbol appear on the left?"
Just select the cells and then use:
ASAP Utilities » Numbers & Dates » Move minus sign from back to front (e.g. 65- to -65)
This will automatically change all number with a trailing minus sign to negative numbers so that you can use it again in your calculations.
Do you recognize any of these situations?
- When using copy/paste from AS400 to Excel, the negative numbers are not recognized as negative numbers because the negative sign remains on the right side of the number. Is there an easy way to flip the negative sign from the right side of the number to the left?
- Our manufacturing system extracts negative numbers in a strange way (at least for me). For instance is lists -134 as 0000134- (for positive, it just list 134). What formula can I write to convert it to -134 and remove the leading zeros and move the - sign to the front?
- I'm importing a COBOL report into Excel and this report, negative numbers have the negative sign in a trailing position. To use cells which contain negative numbers in an Excel formula, the negative sign needs to appear in front of the number. To be able to write formulas using these cells, I have to change the negative number cells to put the negative sign in a leading position.
Other than change each cell manually (which is what I've been doing), is there some way to accomplish this re-format using a macro or something else? - We received an Excel file that has negative numbers with a trailing (instead of leading) negative sign. These are cash transactions from a cash register. Is there a way to reformat only those numbers (there are tens of thousands of transactions) that have a trailing negative sign to make them a negative number?
Just select the cells and use the following tool:
ASAP Utilities » Numbers & Dates » Move minus sign from back to front (e.g. 65- to -65)
If you are importing a text file that has the trailing negative numbers, then (starting from Excel 2002), you can let Excel fix the trailing negative numbers during the import. This article from Microsoft shows how to do that. Debra Dalgleish even explains nicely how you can use this same built-in feature from Excel to fix already imported numbers with negative signs at the end.
Bonus tips, also interesting
How much time will it save?
It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.
Download
In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page