R
Red_Star20
I have this sheet full of stock data and the market cap is all given in the
following format... 102.3M or 12.5B I want to be able to convert these
numbers into numbers with zeros in them.
I have this script that i looked up but it is not working... my guess that
it only works when something changes on the sheet.... like if i enter a
number like
125M then it converts it to 1250,000,000....
what i need however is a something that changes my preentered data into
numbers..... e.g. 100.1M = 100,100,000
any suggestions?
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Right(Target.Value, 1)) = "M" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000000
ElseIf UCase(Right(Target.Value, 1)) = "K" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000
End If
End Sub
following format... 102.3M or 12.5B I want to be able to convert these
numbers into numbers with zeros in them.
I have this script that i looked up but it is not working... my guess that
it only works when something changes on the sheet.... like if i enter a
number like
125M then it converts it to 1250,000,000....
what i need however is a something that changes my preentered data into
numbers..... e.g. 100.1M = 100,100,000
any suggestions?
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Right(Target.Value, 1)) = "M" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000000
ElseIf UCase(Right(Target.Value, 1)) = "K" Then
Target.Value = Left(Target.Value, Len(Target.Value) - 1) *
1000
End If
End Sub