ExcelBanter - Auto Correction

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Auto Correction (https://www.excelbanter.com/excel-discussion-misc-queries/112751-auto-correction.html)

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

Hi

Format the cell with a custom format
FormatCellsNumberCustom #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.

--
Regards

Roger Govier

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

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:

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

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. (^_^)

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

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

"Hi_no_Tori" wrote:

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. (^_^)

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

--
Regards

Roger Govier

"Hi_no_Tori" wrote in message
...
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

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

"Stefi" wrote:

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

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:
FormatCellsNumberCustom #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

"Roger Govier" wrote:

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

--
Regards

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" wrote in message
...
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:
FormatCellsNumberCustom #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

"Roger Govier" wrote:

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

--
Regards

Roger Govier