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

One Way (I am sure there is a better one, but this works):

If 102.3M is is in cell A2, try the following:

=IF(RIGHT(TRIM(A2),1)="K",LEFT(TRIM(A2),LEN(TRIM(A 2))-1)*1000,IF(RIGHT(TRIM(A2),1)="M",LEFT(TRIM(A2),LEN (TRIM(A2))-1)*1000000,IF(RIGHT(TRIM(A2),1)="B",LEFT(TRIM(A2), LEN(TRIM(A2))-1)*1000000000,A2)))

May have to adjust for spaces between your number and your letter (i.e.
125.3 M).

HTH,

ryanb.
xl2003


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

You could almost use the same code--but in a general module.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
If UCase(Right(myCell.Value, 1)) = "B" Then
myCell.Value _
= Left(myCell.Value, Len(myCell.Value) - 1) * 1000000000
ElseIf UCase(Right(myCell.Value, 1)) = "M" Then
myCell.Value _
= Left(myCell.Value, Len(myCell.Value) - 1) * 1000000
ElseIf UCase(Right(myCell.Value, 1)) = "K" Then
myCell.Value _
= Left(myCell.Value, Len(myCell.Value) - 1) * 1000
End If
Next myCell

End Sub

Select the range to adjust (use click and ctrl-click if it's discontiguous) and
run this code.

Red_Star20 wrote:

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

--

Dave Peterson