Make Excel know 1M is 1,000,000

[amirstal]

How can I make excel know that when I type 1M in a cell the number
1,000,000 should appear in that cell (2M will appear as 2,000,000
etc)?

Thanks,

Amir

 

[Guest]

Tools=>Autocorrect options . . .

 

[amirstal]

Tools=>Autocorrect options . . .

Thanks. But is there a way to make a rule so I won't have to enter all
manually.
For example, excel should know that 1.2m means 1200000 and 3.873m is
3873000...

 

[Guest]

If you want it to change in the cell, I believe you would need to write code
that would parse out the entry and do the appropriate multiplication using
the change event.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If LCase(Right$(.Value2, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[amirstal]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If LCase(Right$(.Value2, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

This is great. Thanks very much.
However, when I try to use with the negative sign, it does not work
for example: if I type -0.3m in order to get -300000, I am getting an
error message saying "The formula you typed contains an error."

 

[Susan]

****if**** you don't need the number for any subsequent formulas, then
you can set a custom format - you wouldn't need the "m", just type 3 &
it would give you 3,000,000
but the problem is that the entry stays "3", not "3,000,000", so for
use in formulas it doesn't work.
(i tried this with adding thousands.......)
(i can't find the reference right this minute - i'm still looking -
post back if you want it - assuming i can find it!)
susan

 

[Bob Phillips]

If you enter it like so 1M- (Excel doesn't like -1M), then you can use

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Right$(.Value2, 1) = "-" Then
If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then
.Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000
End If
Else
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[amirstal]

If you enter it like so 1M- (Excel doesn't like -1M), then you can use

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Right$(.Value2, 1) = "-" Then
If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then
.Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000
End If
Else
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks. The second solution did not work as it multiplies every number
by 100000 - even one without an "m". I can use the first solution you
gave me and write "0.3-m" in order not to confuse excel, unless you
are willing to look at the second solution one more time and fix it.

 

[Bob Phillips]

I think that this does it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Right$(.Value2, 1) = "-" Then
If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then
.Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000
End If
Else
If LCase(Right$(.Value2, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)