Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Let's say I have a list of the following numbers:

0.02

-0.25

-0.04

-4.13

-1.12

5.24

-2.12

-11.36

40.61

-141.20

-1.82

-6.48

How would I align them in a column such that they are left-justified in the cell but the decimal points align. I was tempted to use the "Accounting" category, but this transformed my negative values by adding parentheses.

I'm using Excel for Macs 2011 if that makes any difference.

share|improve this question

migrated from stackoverflow.com Mar 26 '13 at 11:26

This question came from our site for professional and enthusiast programmers.

5 Answers 5

After you left-justify the cell, try this number format:

_-???0.0??;-???0.0??;

To apply it, right-click on the cell, select "Format Cells", go to "Number" tab, go to "Custom" format option, and paste the format string into the "Type:" field.

How it works:

  • left most _- inserts whitespace equal to width of a "-" character;
  • ? takes up space equal to that of a number if the number is not present;
  • semicolon divides the format for the positive number (first part) from the format for the negative number (second part).

More in Excel number formats: Number format codes.

Note that this format will work only for numbers with up to 4 digits to the left of the decimal point. If you'll have larger numbers, you'll need to add more question marks.

share|improve this answer
    
That's awesome! Now there's this weird space between the number and the negative sign, though. Any advice for fixing that? –  Atticus29 Mar 14 '13 at 19:28
    
I guess, alternatively, is there a symbol like "?" that can include characters as well as digits (so that we can make excel treat the negative sign as part of the number)? –  Atticus29 Mar 14 '13 at 19:49
    
@Atticus29 hmm, I'll have to take a look. To be honest, it's not something I've done before, I just happened to know how to find the Excel number formatting guide. –  ikh Mar 14 '13 at 20:33
    
Any help would be greatly appreciated. Thanks for bringing the guide to my attention. I've been toying around with it a little and stil can't quite get what I want. –  Atticus29 Mar 14 '13 at 20:42

If I understand correctly, you should just be able to Right Align the column (to line up the decimal) and then Increase Indent until your largest possible number is "aligned" along the left side. This way you won't have to worry about special formatting.

share|improve this answer
    
CEN7272, this is the conclusion I arrived at, too. It requires a bit of manual work with whitespace, though. I was hoping there was a more automatic way? –  Atticus29 Mar 25 '13 at 17:05

I wasn't sure if I should have just commented on my other answer but I figured since this is a new train of thought I'd add a new one....

What about Conditional Formatting? It will require a bit of initial setup but it will provide you the opportunity to have a seperate Number Format for 2 digit numbers, 3 digit numbers, etc as well as their negative counterparts. This way you'd be avoiding the blank spaces between the negagive sign and the number. Plus then you could just copy the formatting for use wherever you need it.

Here's a Conditional Formatting rule that would check if the value in the current cell is between 0 and 9 [1 digit num]:

=(INDIRECT(ADDRESS(ROW(),COLUMN()))>0)*(INDIRECT(ADDRESS(ROW(),COLUMN()))<10)

Note that with conditional formatting, =AND() doesn't work as nicely as one would think so multiplying the 2 boolean values provies you the equivelent expression (Thanks to this post for that tip)

share|improve this answer
    
Hmmm. I like this. Let me play with it. –  Atticus29 Mar 25 '13 at 19:20

Here's the understanding proccess, but at the end there's a single formula, you can jump to it if you like.

First you make a column containing the position of the separator (that's the point symbol ".")

Supposing your numbers are in the A column:

B1 = find("."; A1)
B2 = find("."; A2)
...

At the end of this column, let's say B20, you put the maximum value of the point position:

B20 = max(B1:B19)

Now, let's calculate how much spaces you need for each number. That's the difference between max point position and current point position:

C1 = $B$20 - B1
C2 = $B$20 - B2
...

And finally, add that spaces. Here's an important thing: use a equally spaced font, like "Lucida Console".

D1 = rept(" "; C1) & A1
D2 = rept(" "; C2) & A2
...

Of course you can try to get it all together in a single formula, but I tried to let it well explained. You could, for example, take a fixed max point position, say 10, and do:

= rept(" "; 10 - find("."; A1)) & A1
share|improve this answer

Use a "Tab" there is a special one under Alignment there is one called Decimal. This will align the decimal according to the position of the tab.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.