MS Excel Convert Abbreviated Number to Long Form 2

[BxWill]

Have MS Excel worksheet with approximately 50,000 records that have a column (column AC) containing abbreviated numbers such as $100K.

I would like to convert the data within this column to the long format such as 100000.



Have tried several approaches including the following;

Creating two helper columns to the immediate right of column AC and 1) replacing the "$" with a blank and displaying the data without the symbol $ in the first helper column (column AD) using the REPLACE function such as;

=REPLACE(AC,1,1,"")

Then, in the second helper column, column AE, I insert a formula such as

=LEFT(AD2,LEN(AD2)-1 * IF(RIGHT(AD2)="K",1000,10000)

This initially appeared to work but involved multiple steps.

[Bold]
Any insight as to a VBA approach whereby I select a column containing the abbreviated data and have it converted to the long format?
[/Bold]

Currently experimenting with VBA such as;

Sub ChangeFromAbbvToLongFmt()

Dim Rng As Range    
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    'Pseudocode
    REPLACE(AC,1,1,"")
    Rng.Value = =LEFT(AD2,LEN(AD2)-1 * IF(RIGHT(AD2)="K",1000,10000)

Next
End Sub

 

[Deniall]

I'd approach it with a User Defined Function such as:

Function KText_to_Number(KText As String) As Double
Dim Text As String, Length As Long
'
Length = Len(KText)
Text = Mid(KText, 2, Length - 2)
KText_to_Number = Text & "000"
'
End Function

I attach a small demo spreadsheet that includes this UDF.

EDIT at 19:34
Added the[ ] & "000"[ ] to the code above (but not to the demo spreadsheet).

 

[combo]

Even without VBA, you can replace "K" by "000". You may replace "$" by "" if excel does not recognize numbers. If you have proper number texts but excel treats them as text, you can put 1 in empty cell, copy it, select range to convert and paste special with multiplying options (or 0 and operation "add"). Both replace and multiplying can be performed after selecting all target cells.
It is not clear for me why you multiply by 10000?
If vba, then the replacement in your code:
[tt]Rng.Formula = Replace(Replace(Rng.Value,"$",""),"K","000")[/tt]

combo