input many zeros problem...

W

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
 
D

Danny@Kendal

William Chan said:
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?
Click to expand...

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
 
D

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

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
 
W

William Chan

Thank you all help.

Beege said:
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
Click to expand...
 
Top