Excel VBA - extracting characters from cell

Discussion in 'Programming' started by chipy, Feb 16, 2010.

  1. chipy

    chipy Golden Member

    Joined:
    Feb 17, 2003
    Messages:
    1,465
    Likes Received:
    2
    hey guys,

    i copy the info from my credit card statement into Excel. the way it copies into Excel is that the merchants' names and the prices i paid are all in one cell. currently, i manually "cut" the prices and put them in another column so that the end result is two columns, one with the merchant's name and another with the price. i would like to automate this using VBA... here is the psudocode:

    1) the right-most information in the cell is always the price so read the characters from right to left

    2) while reading the characters, copy them until you get to a character that's not a number

    3) paste the copied characters (which should all be numbers) in the next column's cell.

    does anyone know how to write that in VBA code? thanks!
     
  2. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,368
    Likes Received:
    18
    Are there decimals places in the prices?
     
  3. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,368
    Likes Received:
    18
    1. open up Excel spreadsheet
    2. open VBA editor (alt-f11 will open it)
    3. Click Insert in menu bar, then click module.
    4. Double click the module and copy and paste the following code into it:

    Code:
    Public Function GetPrice(ByVal CellValue As String) As String
    Dim i As Long
    Dim wrk As Worksheet
    Dim strPrice As String
    Dim done As Boolean
    
    i = 1
    'Get the position of the first number
    strChar = Mid(CellValue, i, 1)
    Do While Not IsNumeric(strChar)
        strChar = Mid(CellValue, i, 1)
        i = i + 1
    Loop
    
    
    'Get Price
    strPrice = strChar
    done = False
    
    Do While Not done
        strChar = Mid(CellValue, i, 1)
        If IsNumeric(strChar) Then
            strPrice = strPrice & strChar
            i = i + 1
        ElseIf strChar = "." Then
            strPrice = strPrice & strChar
            i = i + 1
        Else
            done = True
        End If
    Loop
    
    GetPrice = strPrice
    End Function
    5. Save module as modGlobal or whatever name you want
    6. In an adjacent cell, type in "=GetPrice(A1)" w/o double quotes. This should return the price as long as the rules you posted in the OP are followed in all instances.
     
  4. PowerEngineer

    PowerEngineer Platinum Member

    Joined:
    Oct 22, 2001
    Messages:
    2,726
    Likes Received:
    7
    If you haven't tried this already, try copying the credit card statement into a text file, save the file, and then open it in Excel. You'll be presented with various options for parsing each line into multiple cells. Still messy, but...
     
  5. chipy

    chipy Golden Member

    Joined:
    Feb 17, 2003
    Messages:
    1,465
    Likes Received:
    2
    KLin, thanks for your quick code output. i haven't had time to really look at the code but it didn't work - primarily my fault as i left out small details in my OP. the following is an example of a line from the credit card statement:

    12/23 TACO BELL AUSTIN TX 4.93

    the 4.93 is the price which i'd like to put into the cell next to it. i apologize if i made you sweat over your awesome code above. the result i got from implementing your code was "112" without the quotes. i will take a look at your code in more depth when i get a chance and see if i can stumble/figure out how to modify it. KLin, out of curiosity, where did you learn VBA? online? through books? i'd like to get into it since we use Excel at work a lot. thanks again!

    PowerEngineer - thanks for your suggestion. i'll have to give that a try, however i'm already bent on doing this through VBA just because i've been wanting to do it that way and also as a learning experience since i want to get back into some programming.
     
    #5 chipy, Feb 18, 2010
    Last edited: Feb 18, 2010
  6. PowerEngineer

    PowerEngineer Platinum Member

    Joined:
    Oct 22, 2001
    Messages:
    2,726
    Likes Received:
    7
    If the price always shows up at the end of the line with a leading space (and no dollar sign) then this will work:

    Public Function GetPrice(ByVal CellValue As String) As Double
    Dim strPrice As String
    GetPrice = Val(Right(CellValue, Len(CellValue) - InStrRev(CellValue, " ")))
    End Function

    The only reason this can't be done with a cell formula is that there're no reverse order (i.e. right to left) string functions available for a worksheet.
     
  7. chipy

    chipy Golden Member

    Joined:
    Feb 17, 2003
    Messages:
    1,465
    Likes Received:
    2
    you da man! what i don't get in your code is "strPrice". you declared it as a type string but i don't see you using it anywhere else. at first i thought maybe you meant to replace "GetPrice =..." with "strPrice =..." but it works great as is.

    can you explain that to me? also, where/how did you learn VBA? books? internet? i think i could pick up the languange and syntax, but where do you go to find all the availble things like "InStrRev()"? thanks man!

    * Note: Also, I use Office 2007 and I had to enable all macros to get it to work. there were other options but i didn't quite understand them and i'll have to look more into it but i'm wondering if there is a way to enable some macros and not all to increase security? *
     
    #7 chipy, Feb 18, 2010
    Last edited: Feb 18, 2010
  8. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,368
    Likes Received:
    18
    I just learned it on the job and groups.google.com.

    http://www.techonthenet.com/excel/formulas/instrrev.php

    That site might help you with vba reference material.

    GetPrice is a function, which means it needs to return a value to whoever is calling it. strPrice is just a local variable inside GetPrice that is used to build the number that you want.

    When the function is done retrieving what it was designed to retrieve, it sets the function return value to be strPrice.
     
  9. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,368
    Likes Received:
    18
    Much shorter and better solution. :D
     
  10. chipy

    chipy Golden Member

    Joined:
    Feb 17, 2003
    Messages:
    1,465
    Likes Received:
    2
    KLin, thanks for answering my questions and the link... i have it in my favorites now so i can reference it when needed.

    i used PowerEngineer's code, but i just commented-out the "strPrice" line and it still works. :)

    Thanks to both of you for helping me out... it would have taken me forever to have figured out how to do that!
     
  11. KLin

    KLin Lifer

    Joined:
    Feb 29, 2000
    Messages:
    29,368
    Likes Received:
    18
    Yea, strPrice never was being used in his code, so it was fine to take it out. :)