double formatting

  • Thread starter Thread starter Marc Wills
  • Start date Start date
M

Marc Wills

Does anyone know how to use superscript formatting? I am trying to put in a
cell 10 to the power of 3, but can not figure it out. I can either make it
all superscript or all regular text. I tried just highlighting the 3 and it
shows that it works while still highlighted, but once unhighlighted it goes
back to normal size. The only thing I have come up with is to use 2
comlumns, but I was just curious if there was a way around this..thanks Marc
 
Hi Marc:

If you don't want to use the number for calculation, you can format it as
text. You will then be able to apply superscript formatting to part of the
cell.

Regards,

Vasant.
 
Hi Marc,

Superscript 3 has an ascii code of 0179. You can use
formatting through XL but it's a bit of a pain. Or you
could use the code number and enter it like this:

=10&char(179)

If you use alot of super/subscript, take a look at this
free add-in from John Walkenbach. It is so much easier
than using XL formatting:

http://j-walk.com/ss/excel/files/supersub.htm

Biff
 
Thanks Biff for the info, but now the problem is when I go to try and use
that cell in a formula it doesnt work. I.E.- 10 to teh 3rd power times 2. It
just gives me a #VALUE,which kind of sucks. So the formula u gave me worked
to jsut display it, but how about being about to use it??? Any ideas? Also,
I tried theWalkenbach approach adn it doesn work either or I am just doing
it wrong..all heklp is most apprecaited. Thansk again. marc
 
Hi Marc,

That's the problem with XL and super/subscripting. Yes, I
knew it would be evaluated as a text string, so that type
of formatting is just for display purposes.

I don't think there is a way to perform calcs on the
number in that format. You may just have to change the way
you enter the number to 10^3.

I do alot of work in chemistry with tons of subscript, so
that's why I use that add-in.

Biff
 
Hi Marc,

Here's a "klunky" work around that will let you keep the
number format with superscript(for purposes of display)
and still perform calcs on it. The key word being "klunky".

Say the valve is in C7, just include this in your formula:

--LEFT(C7,2)^3

=--LEFT(C7,2)^3*2 = 2000

That's the best I can come up with.

Biff
 
Ok I figured that would be the case....but how do you get the up arrow....I
dont see it on the keyboard anywhere. thanks
 
I am actually trying to use it in a formula....so that I can make a decimal
table, binary table, hexidecimal table and octal table for my class
 
Hi Marc,

Boy, we're really butchering this aren't we?

OK, I think I may have a solution but it uses a helper
column like you suggested you were going to use in the
first place.

Say for example the value 10 is in A1. In the helper
column cell B1 is the value 3. You have the 3 formatted as
superscript. Use whatever method you like. Since the 3 is
now a text value you can use this small formula to get the
desired result or include this string in any formulas that
refer to these values:

=A1^--B1

The power of symbol " ^ " is located in the number keys
above the qwerty keys. Shift 6.

Also, using seperate columns makes referencing these
values much simplier and easier to use in any formula vs
having to hard code as in the earlier attempt.

Biff
 
Biff,

Superscripting doesn't work here because you don't have text in the cell,
but a formula (even though it results in text).

If you put your number in a column, the exponent in another, The following
event macro can build the expression in another column for you and format it
for the superscript. The result column must be formatted text. You can
paste this from here right into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Const BaseNumberColumn = 2 ' set up columns
Const ExponentColumn = 3
Const ResultColumn = 4

' are we in the input cells?:
If Not Intersect(Target, Cells(1, BaseNumberColumn).EntireColumn) Is Nothing
_
Or Not Intersect(Target, Cells(1, ExponentColumn).EntireColumn) Is Nothing
Then

'Concatenate the base and exponent - put it Result Column:
Cells(Target.Row, ResultColumn) = Cells(Target.Row, BaseNumberColumn) &
_
Cells(Target.Row, ExponentColumn)

' Reset all prior superscript formatting:
Cells(Target.Row, ResultColumn).Font.Superscript = False

'Apply superscript formatting to characters:
Cells(Target.Row, ResultColumn).Characters(Start:=Len(Cells(Target.Row,
_
BaseNumberColumn)) + 1, Length:=Len(Cells(Target.Row, ExponentColumn)))
_
.Font.Superscript = True
End If
End Sub
 
Hi Earl,

Thanks for the info. I think I arrived at a suitable
soulution using a helper column. I tested and it works
just fine unless I'm missing something?

This is another one of those basic math functions that XL
should be able to handle but can't without user ingenuity!

Biff
-----Original Message-----
Biff,

Superscripting doesn't work here because you don't have text in the cell,
but a formula (even though it results in text).

If you put your number in a column, the exponent in another, The following
event macro can build the expression in another column for you and format it
for the superscript. The result column must be formatted text. You can
paste this from here right into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Const BaseNumberColumn = 2 ' set up columns
Const ExponentColumn = 3
Const ResultColumn = 4

' are we in the input cells?:
If Not Intersect(Target, Cells(1,
Click to expand...
BaseNumberColumn).EntireColumn) Is Nothing
_
Or Not Intersect(Target, Cells(1,
Click to expand...
ExponentColumn).EntireColumn) Is Nothing
 
Back
Top