×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Excel Convert Abbreviated Number to Long Form
2

MS Excel Convert Abbreviated Number to Long Form

MS Excel Convert Abbreviated Number to Long Form

(OP)
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;

CODE

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

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

CODE

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

This initially appeared to work but involved multiple steps.


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


Currently experimenting with VBA such as;


CODE

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 


RE: MS Excel Convert Abbreviated Number to Long Form

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

CODE

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

RE: MS Excel Convert Abbreviated Number to Long Form

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:
Rng.Formula = Replace(Replace(Rng.Value,"$",""),"K","000")

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close