Make Excel know 1M is 1,000,000
 

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

Make Excel know 1M is 1,000,000
 

Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy



"amirstal" wrote:

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

Make Excel know 1M is 1,000,000
 

On Jun 20, 11:50 am, Tom Ogilvy
wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

Make Excel know 1M is 1,000,000
 

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.

--
Regards,
Tom Ogilvy


"amirstal" wrote:

On Jun 20, 11:50 am, Tom Ogilvy
wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

Make Excel know 1M is 1,000,000
 

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" wrote in message
oups.com...
On Jun 20, 11:50 am, Tom Ogilvy
wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

Make Excel know 1M is 1,000,000
 

On Jun 20, 12:28 pm, "Bob Phillips" wrote:
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" wrote in message

oups.com...

On Jun 20, 11:50 am, Tom Ogilvy
wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

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."

Make Excel know 1M is 1,000,000
 

****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


On Jun 20, 2:03 pm, amirstal wrote:
On Jun 20, 12:28 pm, "Bob Phillips" wrote:





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

roups.com...

On Jun 20, 11:50 am, Tom Ogilvy
wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

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."- Hide quoted text -

- Show quoted text -

Make Excel know 1M is 1,000,000
 

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)



"amirstal" wrote in message
ups.com...
On Jun 20, 3:16 pm, "Bob Phillips" wrote:
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" wrote in message

ups.com...

On Jun 20, 12:28 pm, "Bob Phillips" wrote:
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" wrote in message

groups.com...

On Jun 20, 11:50 am, Tom Ogilvy

wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
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

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...

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."

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.