aligning by decimal point - negative and positive numbers

[Melissa]

I am trying to align my columns by decimal point. I have both negative and
positive numbers, as well as asterisks (to show statistical significance) and
so using Format|Cells|Number and trying the number, currency, and accounting
options don't seem to work, nor does the custom option when I try ##.???? or
-#.????.

For example, I have the following numbers I want to line up:
-0.2684***
-0.2602***
0.7165**
1.0023

Any suggestions?

 

[Niek Otten]

This is for 5 digits before and 8 after the period:

=REPT(" ",5-LEN(LEFT(A1,FIND(".",A1))))&LEFT(A1,FIND(".",A1))&RIGHT(A1,LEN(A1)-FIND(".",A1))&REPT("
",8-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to align my columns by decimal point. I have both negative and
| positive numbers, as well as asterisks (to show statistical significance) and
| so using Format|Cells|Number and trying the number, currency, and accounting
| options don't seem to work, nor does the custom option when I try ##.???? or
| -#.????.
|
| For example, I have the following numbers I want to line up:
| -0.2684***
| -0.2602***
| 0.7165**
| 1.0023
|
| Any suggestions?

 

[Niek Otten]

Note that there is probably a space where the line wraps. There are no two consecutive quotation marks in this formula. If there
seems to be, there should be a space in-between.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| This is for 5 digits before and 8 after the period:
|
| =REPT(" ",5-LEN(LEFT(A1,FIND(".",A1))))&LEFT(A1,FIND(".",A1))&RIGHT(A1,LEN(A1)-FIND(".",A1))&REPT("
| ",8-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I am trying to align my columns by decimal point. I have both negative and
|| positive numbers, as well as asterisks (to show statistical significance) and
|| so using Format|Cells|Number and trying the number, currency, and accounting
|| options don't seem to work, nor does the custom option when I try ##.???? or
|| -#.????.
||
|| For example, I have the following numbers I want to line up:
|| -0.2684***
|| -0.2602***
|| 0.7165**
|| 1.0023
||
|| Any suggestions?
|
|

 

[Ron Rosenfeld]

I am trying to align my columns by decimal point. I have both negative and
positive numbers, as well as asterisks (to show statistical significance) and
so using Format|Cells|Number and trying the number, currency, and accounting
options don't seem to work, nor does the custom option when I try ##.???? or
-#.????.

For example, I have the following numbers I want to line up:
-0.2684***
-0.2602***
0.7165**
1.0023

Any suggestions?

I don't know how you are adding on the asterisks -- but if you are just
concatenating them, then those values will be TEXT and the others numeric. In
any event, you won't be able to line them up unless you know how many asterisks
are present.

But, if you could use a different number format depending on the statistical
significance, and if the maximum number of asterisks is 4, then following
formats should work (with a right-aligned cell:

No Asterisks:

_-#,##0.0000_*_*_*_*;-#,##0.0000_*_*_*_*;0.0000_*_*_*_*;@

1 Asterisk:
_-#,##0.0000"*"_*_*_*;-#,##0.0000"*"_*_*_*;0.0000"*"_*_*_*;@

2 Asterisks:
_-#,##0.0000"**"_*_*;-#,##0.0000"**"_*_*;0.0000"**"_*_*;@

3 Asterisks:
_-#,##0.0000"***"_*;-#,##0.0000"***"_*;0.0000"***"_*;@

4 Asterisks:
_-#,##0.0000"****";-#,##0.0000"****";0.0000"****";@

--ron