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" <(E-Mail Removed)> wrote in message news:B7C01F05-BBE3-4F3D-B676-(E-Mail Removed)...
|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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message news:B7C01F05-BBE3-4F3D-B676-(E-Mail Removed)...
||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 <(E-Mail Removed)>
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