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?

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

"Melissa" wrote in message ...
|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?

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

"Niek Otten" wrote in message ...
| 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
|
| "Melissa" wrote in message ...
||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?
|
|

On Thu, 19 Jun 2008 09:38:01 -0700, Melissa
wrote:

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

I know this post might be a bit outdated, but still:
has anyone been able to solve this problem?!
If so, I´d be happy, if you could help me here!
The solution of Ron Rosenfeld unfortuenately does not work for me.




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][/color]