I need to reformat numbers in Excel. Currently they are in the format 1234.56-. I need to make them -1234.56. That is, the negative indicator needs to be moved from the right side to the left side. I tried using the Format, but it does not recognise numbers with trailing signs as numeric. Short of manually moving them, how do I do it?
I can’t make my numbers appear the way you can’t stop them from appearing. What version of Office are you using? Are you copying the numbers from a different program that puts the - at the end?
I’ve never seen negatives formatted in that way - is Excel recognising them properly as they are? ie if you do a formula like 5 + <cell_reference>, does it actually calculate correctly?
Thought: when you look at the formula bar, does the entry have a comma in front of it? That’s a way of telling Excel not to format whatever you put in the cell. I’m wondering if that’s how those ‘numbers’ were entered?
We’re using Excel 2003. (This causes headaches for me with a couple of large files, due to its record limitation. We’re a non-profit though, so we’re not going to spend the money on 2007 just for a couple of files.)
Trailing negative signs are often used in accounting. Most of the files I receive are Excel files. I have no idea how the businesses who send them create them. Everyone seems to use a different system. Since trailing negatives are not recognised as numbers, calculations using them are not correct. Cells to not have commas to tell Excel not to reformat them.
if 1234.56- si in cell A1 and you want the -1234.56 in cell A2, in cell 2 type:
=-LEFT(A1,LEN(A1)-1)
Assuming it’s a huge spreadsheet, here’s a possible solution:
Sort that column so that these cells end up together
Do a Find and Replace, and replace the - with a blank
Add another column and fill it with -1’s
Add another column and multiply the two columns together (to make the numbers negative)
Paste special as values the numbers back to the original column
A bit roundabout, but hopefully it would work!
elegant.
in a new cell - assuming the data is in column A
=IF(RIGHT(A1,1)="-",(LEFT(A1,(LEN(A1)-1)))-1,A11)
I think that Excel is treating a cell with a number followed by “-” as a string of characters, not as a negative number. So you need to strip off the “-” and multiply by -1. If your cell is A1, then putting this formula into another cell will do the trick:
=LEFT(A1,len(A1)-1)*(-1)
You would then need to copy the value of that back into cell A1.
If you have a whole column or block of these cells, then put that formula (adjusted to the name of the first cell in your column or block) into a vacant part of your spreadsheet, copy the formula to a block of the right size, then copy the values in that block back into the original block of cells.
ETA: while I was writing that, others got there faster!
This works if the number is always negative.
Otherwise, use the formula I posted.
:smack: I did the first two steps. But then I tried to do a Custom format to add minus signs. (When I calculated, the numbers were still taken as positive.) I should have thought about multiplying by -1 and pasting Special. I’m already calculating the Balances, and I often do various ‘off spreadsheet’ calculations. I guess I need more coffee this morning. Anyway, it worked.
I’ll try the formulas later. First half of the month, and there’s a lot of data. I’ll have to try it when I have more time.
It would be SO much easier if people sent us fixed-block ASCII text files! (The program I wrote – which required FB .txt – parses amount fields for non-numeric characters and takes dashes into account. But this file doesn’t have a total, which needs to be added before converting to text – unless I want to rewrite the subroutine, which I don’t.)
Thanks for the help! 
I also found this:
Probably too late to actually matter, but you don’t need to copy an entire column of -1 in order to do this. You can just set one cell equal to -1 and then copy it. Next, select the cells you wish to turn negative, select Paste Special, click the Multiply and Value buttons, and then paste.
Thanks for that. I’ll check it out.
Since five of the columns had few negative numbers, I just did those by hand. For the last column, which contained 89 negatives, I went to the next (empty) column and typed = [cell with neg] * (-1) and copied the formula into all of the cells, then pasted the results as Values into the original cells and deleted the ‘workspace’ column.