input many zeros problem...

[William Chan]

Hi Everyone,

My boss told me that he is a lazy guy.
example: cell A1
input value: 500,000,000

He found too many zeros should be input and he try to minizes the input with
500,000k. However, I found it cannot do calculation of this input. Can
some body help me?

The objective is minize the input of zeros and it still can using for
calculation.

Best Regards,
William Chan

 

[Guest]

Use two cells:

In A1 enter 500000 and in B1 enter=A1*100

 

[Danny@Kendal]

Hi Everyone,

My boss told me that he is a lazy guy.
example: cell A1
input value: 500,000,000

He found too many zeros should be input and he try to minizes the input
with 500,000k. However, I found it cannot do calculation of this input.
Can some body help me?

5E+8 is shorthand for 500,000,000 (ie: 5 x 10^8) and should be interpreted
as a number by Excel.

eg:
5K = 5,000 = 5E+3
5M = 5,000,000 = 5E+6
5G = 5,000,000,000 = 5E+9

Other than that, I think you'll need a custom function. Something which
looks at the right-most character, if it's K then multiply what's left by
1000, if M then multiply by 1000,000, etc.

I quickly knocked together this rough-and-ready function which seems to
work. I've only recently begun writing custom functions and scripts for
Excel so comments and criticisms would be most welcome.

' Convert numbers such as 10K to 10,000
Function fnKMG(strTextNum) As Long
Dim strScale, strNumber As String
Dim multiplier As Long
strScale = Right(strTextNum, 1)
strNumber = Left(strTextNum, Len(strTextNum) - 1)
multiplier = 1
Select Case strScale
Case "K"
multiplier = 1000
Case "M"
multiplier = 1000000
Case "G"
multiplier = 1000000000
Case "0" To "9"
multiplier = 1
strNumber = strNumber & strScale
End Select
fnKMG = CLng(strNumber * multiplier)
End Function

 

[Dana DeLouis]

Here's another idea:
Tools | Options | Edit Tab
Check "Fixed decimal places", and select -8
Now, if you enter 5, Excel will add the 8 zeros.
You could record a macro to quickly turn this feature on/off.

 

[Beege]

You could format cells/custom

###,###",000""000".

but remember to multoply in subesquent formulae by 10^6 (or use a helper
cell to create the actual number.

Beege

 

[William Chan]

Thank you all help.

You could format cells/custom

###,###",000""000".

but remember to multoply in subesquent formulae by 10^6 (or use a helper
cell to create the actual number.

Beege