custom number formatting

[lezawang]

Hi
I want to write a code to format numbers like the following

[TABLE="width: 500"]
<tbody>[TR]
[TD]number[/TD]
[TD]appears in excel[/TD]
[/TR]
[TR]
[TD]1200[/TD]
[TD]1,2 K
[/TD]
[/TR]
[TR]
[TD]1599[/TD]
[TD]1,599 k[/TD]
[/TR]
</tbody>[/TABLE]

I found this code #,##0,
but did work as expected. Thank you very much.

 

[mumps]

Try custom formatting using: #,##0 k

This will not remove the zero's, however.

 

[Joe4]

mumps,

I think they are using a system where a decimal point represented by a comma, not a period.
Hence, 1,2 K represents one-thousand two-hundred and 1,599 K represents one-thousand, five hundred, and ninety-nine.
So the "K" is just short-hand for "thousand".

lezawang,
Is that correct?
Are you regional settings in Excel already set up to use a comma instead of a period to indicate decimals?

 

[mumps]

Joe4: Thank you for that.

This formula might work: =A1/1000 & " k"

 

[lezawang]

Thank you all. Yes that is right. The problem with this code #,##0 k
when I type 300, I will get 300 K, and when I type 3 I get 3 k
What I want to do is when I type
3 I get 3
and when I type 1000, i get 1k
and when I type 1500, I get 1,5 K

is that possible? Thank you all again.

mumps,

I think they are using a system where a decimal point represented by a comma, not a period.
Hence, 1,2 K represents one-thousand two-hundred and 1,599 K represents one-thousand, five hundred, and ninety-nine.
So the "K" is just short-hand for "thousand".

lezawang,
Is that correct?
Are you regional settings in Excel already set up to use a comma instead of a period to indicate decimals?

 

[mumps]

Assuming you will be entering data in column A, place this macro in the code module for the worksheet (not a standard module).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Len(Target) > 3 Then
        Target = Target / 1000 & " K"
    End If
    Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Hi
I want to write a code to format numbers like the following

[TABLE="width: 500"]
<tbody>[TR]
[TD]number[/TD]
[TD]appears in excel[/TD]
[/TR]
[TR]
[TD]1200[/TD]
[TD]1,2 K
[/TD]
[/TR]
[TR]
[TD]1599[/TD]
[TD]1,599 k[/TD]
[/TR]
</tbody>[/TABLE]

I found this code #,##0,
but did work as expected. Thank you very much.

I am using a system where a dot is my decimal point. What I don't know on your system is whether you use a dot or a comma for the decimal point when you do a Custom Format. Assuming you use a comma (meaning the dot is your thousands separator), try using this custom format...

0,0#######. K