Auto Correction

H

Hi_no_Tori

Hi...
Can I format a cell, so that when I enter for example: "120 mm2", the "2" is
converted automatically to superscript.
 
R

Roger Govier

Hi

Format the cell with a custom format
Format>Cells>Number>Custom> #0.0 "mmAlt0178"

Holding down the Alt button, whilst typing 0178 on the Numeric keypad,
will create the superscript 2 as soon as you release the Alt key and it
should appear as mm²

Change the number format #0.0 to suit the precision you wish to display.
This method has the advantage that any numbers entered to the cells can
still be used in further calculations.
 
S

Stefi

You can do that with this event sub:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Characters(Start:=InStr(1, Target, "mm2") + 2,
Length:=1).Font.Superscript = True
End Sub

You can refine the sub if you want to apply it only to a certain range, e.g.
column A:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Target.Characters(Start:=InStr(1, Target,
"mm2") + 2, Length:=1).Font.Superscript = True
End Sub

Regards,
Stefi


„Hi_no_Tori†ezt írta:
 
H

Hi_no_Tori

Hi Stefi
I tried the code you have given me. It's working, so than you SO MUCH for
your help.

I have another related question, and I'll be really thankful if you can
help. How can I refine the sub to make it apply to only a single cell (e.g.
D12)?

Many thanks again. (^_^)
 
H

Hi_no_Tori

Hi Roger...

Thanks for respnding quickly to my question. I tried your method, but it
didn't work. Please, check the method you have given to me, to see whether
it's working properly or not.

THANK YOU
 
S

Stefi

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D12" Then
Target.Characters(Start:=InStr(1, ActiveCell, "mm2") + 2,
Length:=1).Font.Superscript = True
End Sub

Regards,
Stefi
 
R

Roger Govier

Hi

Tried it out before sending, and it worked perfectly.
Are you using the numeric keypad to enter 0178 whilst holding down the
Alt key?
It will not work if you use the numbers on the top row of your keyboard.

If you are using a laptop computer without a numeric pad, there is
usually a function button plus a key which will invoke a "pseudo"
numeric pad using the
789
UIO
JKL
M
keys on the keyboard.

Try switching on the "pseudo" numeric keyboard after typing the "mm and
before you type the Alt+0178
 
H

Hi_no_Tori

Hi...

It didn't work at first. When I enter for example "120 mm2", the "2" in 120
becomes superscript instead of the "2" in "mm2". I replaced "ActiveCell"
with "Target" and now it's working properly. the problem is solved. Thank you
VERY MUCH, Stefi. (^_^)

Regards,
Hi_no_Tori
 
H

Hi_no_Tori

Hi Roger

I'm not sure whether you have understood my need or not. I'll try to
clarify... Here is what I wanted to do again:

When I type for example "120 mm2" in a cell (e.g. D12) and press enter, I
want the font of the "2" in "mm2" to be converted automatically to
superscript.

I tried formatting the cell D12 as you have told me:
Format>Cells>Number>Custom> #0.0 "mmAlt0178"

but it didn't work... When I type "120 mm2" and press enter, it remains just
the same.

regards,
Hi_no_Tori
 
R

Roger Govier

Hi

I now see the problem, I should have been more explicit.
Having formatted the cell as described, all you need to enter is 120.
What will be displayed (because of the formatting) will be 120 mm²
As I said originally, the advantage of this method is that the number
(120) can be utilised in any further calculation, as it is still purely
a number.
--
Regards

Roger Govier


Hi_no_Tori said:
Hi Roger

I'm not sure whether you have understood my need or not. I'll try to
clarify... Here is what I wanted to do again:

When I type for example "120 mm2" in a cell (e.g. D12) and press
enter, I
want the font of the "2" in "mm2" to be converted automatically to
superscript.

I tried formatting the cell D12 as you have told me:
Format>Cells>Number>Custom> #0.0 "mmAlt0178"

but it didn't work... When I type "120 mm2" and press enter, it
remains just
the same.

regards,
Hi_no_Tori
Click to expand...
 
Top