Number Formats

G

greasyboy6

I've been working with number formats lately. I wrote in the custo
format ???.??? to align a set of numbers by their decimals. Fo
example, 1.25, 12.45, and 137.98 would all align by the decimal eve
though they take up different amounts of text.

The problem is that I want to add superscript letters after some of th
numbers. So I would want an "a" to come after 1.25 in superscript.
When I add the "a," the number format no longer works and the number
do not align by the decimals.

How can I add the letter and still make the number format ???.??
work?

Thanks
 
E

Earl Kiosterud

Greasy,

Superscripting is for text, and your format code is for numbers. One
solution is for a macro to place your numbers in as text, with the a, and do
the character superscripting. The resulting text won't be accessible to
formulas as numbers (you can't add them up and stuff).

Or you could put the a in an adjacent column. You can put a white border
(Format - Cells - Border) in to disable the gridline between the cells. It
doesn't look too bad. We do the best we can. :)
 
G

greasyboy6

Thanks for the response,

I tried the second option you posted and it worked. The only proble
is that the text is too far away from the number.

As far as the first option goes, how do you enter numbers as text? Fo
example, would I record a macro where I type in 1.25a. Is the 1.25
text? Also, would the superscripting occur after or during the macro?

Thanks agai
 
E

Earl Kiosterud

Greasy,

The best you can probably do is to right-align the number and left-align the
a

Typing 123.456a becomes text when such data is keyed in, regardless of the
formatting of the cell.

This proc, in the Sheet module, will automatically superscript the last
character after it's been entered. See if this is in the right direction:

Private Sub Worksheet_Change(ByVal Target As Range)
' are we in column A?
If Not Intersect(Target, Cells(1, 1).EntireColumn) = True Then
If Not IsNumeric(Right(Target, 1)) Then
Target.Characters(Start:=Len(Target), Length:=1).Font.Superscript = True
End If
End If
End Sub
 
R

Ron Rosenfeld

I've been working with number formats lately. I wrote in the custom
format ???.??? to align a set of numbers by their decimals. For
example, 1.25, 12.45, and 137.98 would all align by the decimal even
though they take up different amounts of text.

The problem is that I want to add superscript letters after some of the
numbers. So I would want an "a" to come after 1.25 in superscript.
When I add the "a," the number format no longer works and the numbers
do not align by the decimals.

How can I add the letter and still make the number format ???.???
work?

Thanks.
Click to expand...

If the superscript you wish to use shows up in the font, then you could use two
custom formats. Superscript a can be entered as <alt><0170> with the 0170 on
the numeric keypad. In the formats below, I'm not sure how it will show up on
your news reader, but the last character is a superscript a.

Where you want the superscript a to appear:
???.???ª

Where you don't want it to appear:

???.???_ª

The underscore says "leave a space the same width as the next character that
follows".

This will leave your decimals lined up.


--ron
 
Top