# Excel question: Formatting numbers **URL:** \ **Category:** Factual Questions\ **Created:** [October 14, 2009, 4:11pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798 "2009-10-14T16:11:04Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 14, 2009, 4:11pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/1 "2009-10-14T16:11:04Z")
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? ---
**Author:** ![Inigo\_Montoya](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/inigo_montoya/32/124_2.png) [@Inigo\_Montoya](https://boards.straightdope.com/u/Inigo_Montoya)\ **Post date:** [October 14, 2009, 4:16pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/2 "2009-10-14T16:16:21Z")
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? ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [October 14, 2009, 4:17pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/3 "2009-10-14T16:17:17Z")
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 + \, does it actually calculate correctly? ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [October 14, 2009, 4:19pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/4 "2009-10-14T16:19:20Z")
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? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 14, 2009, 4:21pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/5 "2009-10-14T16:21:55Z")
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. ---
**Author:** ![jujuju](https://avatars.discourse-cdn.com/v4/letter/j/7993a0/32.png) [@jujuju](https://boards.straightdope.com/u/jujuju)\ **Post date:** [October 14, 2009, 4:22pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/6 "2009-10-14T16:22:50Z")
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) ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [October 14, 2009, 4:25pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/7 "2009-10-14T16:25:58Z")
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! ---
**Author:** ![Inigo\_Montoya](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/inigo_montoya/32/124_2.png) [@Inigo\_Montoya](https://boards.straightdope.com/u/Inigo_Montoya)\ **Post date:** [October 14, 2009, 4:26pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/8 "2009-10-14T16:26:25Z")
> [@jujuju](#): > > =-LEFT(A1,LEN(A1)-1) elegant. ---
**Author:** ![pan1](https://avatars.discourse-cdn.com/v4/letter/p/e495f1/32.png) [@pan1](https://boards.straightdope.com/u/pan1)\ **Post date:** [October 14, 2009, 4:27pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/9 "2009-10-14T16:27:11Z")
in a new cell - assuming the data is in column A =IF(RIGHT(A1,1)="-",(LEFT(A1,(LEN(A1)-1)))_-1,A1_1) ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [October 14, 2009, 4:29pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/10 "2009-10-14T16:29:36Z")
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! ---
**Author:** ![pan1](https://avatars.discourse-cdn.com/v4/letter/p/e495f1/32.png) [@pan1](https://boards.straightdope.com/u/pan1)\ **Post date:** [October 14, 2009, 4:29pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/11 "2009-10-14T16:29:42Z")
> [@jujuju](#): > > 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) This works if the number is always negative. Otherwise, use the formula I posted. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 14, 2009, 4:39pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/12 "2009-10-14T16:39:23Z")
> [@sandra\_nz](#): > > 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! :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! 🙂 ---
**Author:** ![DanBlather](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@DanBlather](https://boards.straightdope.com/u/DanBlather)\ **Post date:** [October 14, 2009, 8:28pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/13 "2009-10-14T20:28:39Z")
I also found this: > [@](#): > > In Excel 2002, and later versions, imported numbers with trailing minus signs can be easily converted to negative numbers. > > Select the cells that contain the numbers > Choose Data\>Text to Columns > To view the Trailing Minus setting, click Next, click Next > In Step 3, click the Advanced button > Check the box for ‘Trailing minus for negative numbers’, click OK > Click Finish > Note: If ‘Trailing minus for negative numbers’ is checked, you can click Finish in Step 1 of the Text to Columns wizard. ---
**Author:** ![regginbrow](https://avatars.discourse-cdn.com/v4/letter/r/ba9def/32.png) [@regginbrow](https://boards.straightdope.com/u/regginbrow)\ **Post date:** [October 14, 2009, 10:00pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/14 "2009-10-14T22:00:55Z")
> [@sandra\_nz](#): > > Add another column and fill it with -1’s! 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. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 14, 2009, 11:27pm UTC](https://boards.straightdope.com/t/excel-question-formatting-numbers/513798/15 "2009-10-14T23:27:48Z")
> [@DanBlather](#): > > I also found this: Thanks for that. I’ll check it out. > [@regginbrow](#): > > 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. 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.