Register
Welcome to the Stormspire.net WoW Gold Forums!

Greetings, guest! Welcome to Stormspire.net, your #1 resource for WoW and Diablo 3 Gold, Professions, and everything in between!


Enjoy what the site offers? Consider Premium Membership if you wish to get the most out of the site. Premium Membership benefits include the acclaimed Goblineer's World of Warcraft Gold Guide, private forum access, a complimentary high-quality avatar, and much more!


If this is your first visit, make sure to check out the Stormspire.net Survival Guide for a quick site orientation. You may also register to unlock all standard forum features, and to get rid of these lovely ads!


We proudly host the Official forums for TradeSkillMaster, The Undermine Journal, and WoWuction.

Results 1 to 3 of 3
  1. #1
    Ord
    US-Garona(H)
    Entrepreneur
    Ord's Avatar
    Posts
    68
    Reputation
    18
    Tagged in
    22 Posts
    Add to this user's reputation

    Post [Excel] Gold (string) to coppers (number) function... and more....

    What Is It?
    Convert formatted money strings into a number format ("coppers") to perform math or sorting functions on (1g 15s 30c -> 11530) and then back again for readability if desired.


    Background/Inspiration
    The TSM API has functions that format or un-format money values as needed for addon functionality or display purposes. Two of these are TSMAPI:FormatTextMoney and TSMAPI:UnformatTextMoney. One day I was copy+pasting the "Component Parts" section of The Undermine Journal to analyse mat costs and potential profits, except the values were formatted money strings (TEXT) and not numeric values that I could add/subtract/average. I thought to myself "It'd be cool if I had those TSM functions that convert money strings to copper" and then decided to just create my own to use in Excel.


    What These Functions Can Do
    1. Return a number value for formatted gold strings in the value of 'coppers' ("500s 50g 50c" converted into the number 550050)
    2. Format a copper value into more commonly seen 'gold-silver-copper format' (550050 converted into "55g 0s 50c")
    3. Work on odd formatted money values like out-of-order currencies or copper/silver values > 99.
    4. They are versatile: they use Regular Expressions (RegEx) to pattern-match the numbers before the currency character.
    5. Can easily incorporate into existing spreadsheets like @Stede 's sandbox or your favorite shuffle spreadsheet.



    What They Can't Do
    1. Excel or VBA doesn't like numbers over ~900 million. There are probably ways to force it, but I figure that should be much higher than any WoW-related value you might actually be working with, so I didn't worry about it.
    2. No option to remove zero-value denominations, for example 500000 will format to "50g 0s 0c" not the simpler "50g"
    3. No commas added to output formatted strings: (will show you 15000g and not 15,000g) -- I use commas for numeral grouping, other people use periods. I don't think either matter in this usage.
    4. No error-handling code.
    5. They will not bake pies or cakes or cookies :(



    Requirements
    1. Excel, probably any version. (Tested using Excel 2010, but working in an .xls legacy filetype as well.)
      (I don't do much in Google Docs so I'm not even sure what kind of syntax/language their scripting uses)
    2. EDIT: No longer a requirement: Microsoft VBScript Regular Expressions 5.5 library reference enabled in your VBA editor
    3. Strings or numbers that you need or want to convert



    The Functions

    ConvMoney()
    This is the function that uses RegEx to find the value of each type of currency. It requires two parameters, the first is a string that may contain gold, silver or copper values in it's text. The second parameter is either "G", "S" or "C" to determine what denomination to extract. It will first remove any spaces and any commas from the string then match any length number that is followed by the currency character. If no number/value for the specified denomination is found, will return zero (0). If a denomination other than g, s, or c is specified, it will return 0. Both the string and the currency char can be dynamic cell references as well as static/hard-coded eg. ConvMoney(A1,"c").

    Examples:
    ConvMoney("30g 50s 20c","S") will return 50, the amount of silver specified in the string.
    ConvMoney("4000c 10g","C") will return 4000, the amount of coppers specified in the string.
    ConvMoney("I like cake", "c") will return 0, since even though there is a "c" in the string, there is no numbers preceding it to value.
    ConvMoney("50g 50s 50c", "Z") will return 0, since "z" is not a recognized currency denomination.



    MoneyToCoppers()
    Inspired by TSMAPI:UnformatTextMoney
    This function is pretty basic, utilizing the aforementioned ConvMoney() function to get each the gold, silver and copper values from the source string, then converts the gold and silver to their copper equivalent, adds them up and returns the sum as it's output.

    Examples:
    MoneyToCoppers("10g 20s 30c") will return 102030
    MoneyToCoppers("30g 25s 19c") will return 302519
    MoneyToCoppers("16s 9g") will return 91600
    MoneyToCoppers("I like cake") will return 0 since no values would be found for any denomination (0+0+0)



    CoppersToMoney()
    Inspired by TSMAPI:FormatTextMoney
    This last function will format a number of coppers into the more readable #g #s #c WoW-currency format. It divides the input number by copper-per-gold (10000), then copper-per-silver (100) for gold and silver amounts, subtracts those quotients (ignoring/flooring decimals for gold and silver) and uses the remainder as coppers. Once it has figured out what the independent gold, silver and copper values are it just smashes them together and returns a string output of the formatted wow gold value. The cool thing with this function is you can "clean up" poor or oddly formatted money strings by combining it with MoneyToCoppers(). See examples below for what I mean:

    Examples:
    CoppersToMoney(490743) would return "49g 7s 43c"
    CoppersToMoney(MoneyToCoppers("55 g45s 54 c")) would return "55g 45s 54c"
    CoppersToMoney(MoneyToCoppers("50 gold, 78 silver, 22 copper")) would return "50g 78s 22c"
    CoppersToMoney(MoneyToCoppers("5989c 200s")) would return "2g 59s 89c"
    CoppersToMoney(MoneyToCoppers("10g 3905s 243c")) would return "49g 7s 43c"




    The Code
    EDIT 05-FEB-2014: Changed code to have RegEx objects created with late-binding. Should be no noticeable difference other than there's no longer a need to add the regex vb reference in the excel code editor.

    Since these functions were meant to work with eachother, I'm supplying the code for all 3 of them together. I made sure to add as many comments to the code as possible to make it easier to understand or modify if desired. All I ask for is credit in your source code if you want to use it in a spreadsheet you distribute yourself.

    Excel VBA Module Code:
    Code:
    Option Explicit 'Seriously, declare your vars first... Variant is bad, mkay?
    
    
    '** Ord's Gold/Math Functions
    '** Source: http://stormspire.net/tools-programs-spreadsheets/13517-%5Bexcel%5D-gold-string-coppers-number-function-more.html#post129941
    
    Function MoneyToCoppers(sMoney As String) As Long
    
    ' This function will take a string passed to it and run it through the MoneyToCoppers() function 3 times, once for each currency type.
    ' The INPUT is any string that might have a wow-type gold value in it.
    ' The OUTPUT will be an unformatted number value, great for sorting or adding/subtracting
    
    
    ' Tell Excel to recalculate this value if/when other cells change
    ' by making sure it is defined as "volatile"
        Application.Volatile (True)
    
    
    ' Declare vars
    ' (Using Long to possibly avoid overflow if the string has a very large number to parse)
        Dim iCopper As Long
        Dim iSilver As Long
        Dim iGold As Long
    
    
    ' Get the value of the three currency types using the ConvMoney() function.
        iCopper = ConvMoney(sMoney, "c")
        iSilver = ConvMoney(sMoney, "s")
        iGold = ConvMoney(sMoney, "g")
    
    
    ' Crunch numbers and return the value
        MoneyToCoppers = (iGold * 10000) + (iSilver * 100) + (iCopper)
    
    
    Finish:
    End Function
    
    
    Function CoppersToMoney(Coppers As Long) As String
    
    ' This function will format a number (value) into WoW Gold/Silver/Copper values
    ' The INPUT is any number (value) (...that isn't too big to kill Excel)
    ' The OUTPUT is a "(x)g (y)s (z)c" WoW currency formatted string
    
    
    ' Tell Excel to recalculate this value if/when other cells change
    ' by making sure it is defined as "volatile"
        Application.Volatile (True)
    
    
    ' Declare vars
    ' (Using Long to possibly avoid overflow if the string has a very large number to parse)
        Dim iCopper As Long
        Dim iSilver As Long
        Dim iGold As Long
    
    
    ' Crunch numbers
        iGold = Int(Coppers / 10000)
        iSilver = Int((Coppers - (iGold * 10000)) / 100)
        iCopper = Int((Coppers - (iGold * 10000) - (iSilver * 100)))
    
    
    ' Return the full formatted string
    CoppersToMoney = iGold & "g " & iSilver & "s " & iCopper & "c"
    
    
    Finish:
    End Function
    
     
    Function ConvMoney(MoneyString As String, CurrencyType As String) As Long
    
    'This function will return the value of a specific curency from an input string
    'The INPUT is a string that might have gold/silcer/copper value AND a denomination type
    'The OUTPUT is the ammount of the currency denomination found in the string **NOT its copper value
    
    
    
    ' Tell Excel to recalculate this value if/when other cells change
    ' by making sure it is defined as "volatile"
        Application.Volatile (True)
    
    
    ' Declare Vars
        Dim strTest As String
        Dim sPat As String
        
    'EDIT-- The RegEx portion was re-written to use late-binding to avoid references headaches
        Dim RE As Object
        Dim REMatches As Object
        Dim REMat As Object
        
        Set RE = CreateObject("vbscript.regexp")
        
    
    ' Remove commas and whitespace from input string so RegEx can match consistently
    ' (Remove any other types of caracters or words by adding subsequent Replace() functions if needed)
        strTest = Replace(MoneyString, ",", "")
        strTest = Replace(strTest, " ", "")
        'strTest = Replace(strTest, "Something else to replace here", "")
    
    
    ' Finally, uppercase all letters to make the CASE and MATCH case-INsensitive
        strTest = UCase(strTest)
        CurrencyType = UCase(CurrencyType)
    
    
    ' Here, we determine what type of currency to return based on the second INPUT value
    ' If not 'g', 's', or 'c' it this function will return ZERO
        Select Case CurrencyType
            Case "G"
                sPat = "(\d+)G"
            Case "S"
                sPat = "(\d+)S"
            Case "C"
                sPat = "(\d+)C"
            Case Else
                'Return ZERO (not an error) if invalid money type
                ConvMoney = 0
                GoTo Finish
        End Select
    
    
    ' Create the RegExp objects
    ' Match pattern is defined in the case block above
        'Set RE = New RegExp (EDIT: not needed w/ late-binding)
            RE.MultiLine = False
            RE.Global = False
            RE.IgnoreCase = True
            RE.Pattern = sPat
    
    
    ' We need to test if there are any matches or else the function will return an #ERROR
        If RE.Test(strTest) Then
            Set REMatches = RE.Execute(strTest)
            Set REMat = REMatches(0)
            'The match includes the currency character, but if we Replace() it all that's left is a number. That number is what is passed as the result.
            ConvMoney = CLng(Trim(Replace(REMat, CurrencyType, "")))
        Else
            'If there are no matches, we simply return a value of ZERO instead.
            ConvMoney = 0
        End If
    
    
    Finish:
    End Function


    Attachment
    I've attached an excel xls file with these functions and example usage if you just want to play with it and see it in action.
    Here is a screenshot of the attached spreadsheet showing some more examples.



    Credits/Thanks
    You. I wouldn't be inspired to share this if I didn't think there wasn't someone out there that could benefit from it.
    @Sapu94 @Bart39 and anyone else that's contributed to the development of TSM in any way.
    @Kathroman @Stede @Sterling @PhatLewts @Mithrildar and everyone else contributing to the community and helping spread their goblin wisdom.
    Attached Files Attached Files
    Last edited by Ord; February 5th, 2014 at 04:36 PM.

  2. #2
    Ord
    US-Garona(H)
    Entrepreneur
    Ord's Avatar
    Posts
    68
    Reputation
    18
    Tagged in
    22 Posts
    Add to this user's reputation
    Big edit:

    I have changed the regexp portion to use late binding. This means you no longer need to mess with your "references" section to make these functions work properly.

    I'll leave the instructions below for historical purposes, but keep in mind they are no longer needed.









    As mentioned in the requirements section, this code requires you manually add an additional reference in your Excel VBA editor references. If you don't have it enabled (it is NOT enabled by default) you will get an error, possibly like this one shown:
    dugqTrG.jpg

    Luckily, it's simple to add the code reference, and should only have to be done once.

    How to add RegEx reference to Excel VBA
    1. Open your VBA Editor (view code)
    2. Click on the "Tools" menu and select "References"
    3. Look for "Microsoft VBScript Regular Expressions 5.5" and check it
      CLkSrtL.jpg
    4. Click "Ok"
    5. Save the file, close the editor and close the spreadsheet
    6. Reopen and all references should be working now.



    Misc/Other:
    I have "Microsoft VBScript Regular Expressions 1.0" as a code reference but haven't tested if it works the same. Selecting more than one might cause weird issues, so just select the highest version that you have and you should be fine.
    Last edited by Ord; February 5th, 2014 at 04:25 PM. Reason: Late-Binding pwnage

  3. #3
    US - Aerie Peak (A)
    Wind Trader
    Stede's Avatar
    Posts
    1,971
    Reputation
    259
    Tagged in
    1077 Posts
    Add to this user's reputation
    Nice!!! Will need to take some more time to pour over all this - thanks!
    Some will; Some won't - So what? Let em die poor.

    +Rep doesn't buy me beer, but it sure gives me a reason to have one - if you learned something new or thought about something differently, let's celebrate!

    Late Nite With Stede - A WoW Goldmaking Blog / Podcast - LNWS.net
    #LNWS on Quakenet IRC
    @StedeWoW on Twitter
    Stede#1203 in-game

 

 

Similar Threads

  1. [Question] Battlepet string?
    By TuckerTSM in forum Official TradeSkillMaster Forum
    Replies: 12
    Last Post: August 17th, 2013, 05:57 PM
  2. [Bug] Can't use a gold amount number more than once in a price string
    By Zucan in forum Official TradeSkillMaster Forum
    Replies: 5
    Last Post: August 16th, 2013, 03:51 PM
  3. Compare string with a number error.
    By delice in forum Official TradeSkillMaster Forum
    Replies: 2
    Last Post: March 3rd, 2012, 12:06 PM
  4. [Bug] Minus coppers in market notification
    By Zero in forum Official Forum of The Undermine Journal
    Replies: 1
    Last Post: November 2nd, 2011, 06:35 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •