How do you convert $100M into a whole number?

[Guest]

As above assuming it is possible. Have tried copy and special paste without
much luck so far.

 

[Guest]

Hi Jerry,

copy this code (press alt+f11) and use in an auxiliar column
=digitsonly(a2)

***********************************************
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
***********************************************

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jerry Lee Penny" escreveu:

 

[joeu2004]

Subject: How do you convert $100M into a whole number?
As above assuming it is possible. Have tried copy and special paste without
much luck so far.

[Please repeat the question in the body of the posting.]

Do you mean that you have the text "$100M"? If so, does the following
meet your needs, assuming that A1 contains the text to convert:

1000000*mid(A1, 2, len(A1)-2)

More robust:

if(isnumber(A1), A1, if(right(A1,1)="M", 1000000*mid(A1,2,len(A1)-2),
if(right(A1,1)="K", 1000*mid(A1,2,len(A1)-2), 1*right(A1,len(A1)-1))))