Decimal Alignment

[Guest]

I am looking for a way to align a column by the decimal point. My data is
both numeric and character. I could make the data all character, but I'm
still not sure how to align string data by a particular character.
i.e.-
101.23**
1.26*
1.11***
12.12*
1.05
Help is appreciated.

 

[Sandy]

Use the cutsom format ???.??? Highlight the range you want rightclick
==> format cells ==> number tab ==> custom, then paste this into the
textbox. Also look in the help section "Guidelines for custom number
formats"

Sandy

 

[Guest]

tried that and it doesn't seem to work. It's my understanding (from reading
other posts) that custom formats only work for numeric data???

 

[Guest]

You can try this formula to pad blank spaces at the end to make the decimals
line up. A couple of things to note... You will need to use a proportional
font such as Courier and the number 5 in the formula refers to thenumber of
characters after the decimal. It must be at least as large as the max number
of characters after the decimal...

 

[Guest]

Thanks Jim,

I think you forgot to include the formula you are suggesting...?

Lois

 

[Guest]

Well that would help...

=A1& REPT(" ", 5 + FIND(".", A1) - LEN(A1))

 

[Guest]

Clever solution, I like it...

I'm still disappointed that Excel doesn't seem to offer an option to align
columns by a particular character. The courier limitation is a bit
frustrating...

Anyway, thanks for the idea, didn't know about the REPT function...

 

[James Silverton]

Hello, Jim!
You wrote on Fri, 27 Oct 2006 13:02:02 -0700:

JT> You can try this formula to pad blank spaces at the end to
JT> make the decimals line up. A couple of things to note...
JT> You will need to use a proportional font such as Courier

But Courier isn't a proportional font!


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not