Excel VBA - extracting characters from cell

[chipy]

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!

 

[KLin]

Are there decimals places in the prices?

 

[KLin]

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:

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.

 

[PowerEngineer]

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

 

[chipy]

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.

 

[PowerEngineer]

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.

 

[chipy]

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.

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? *

 

[KLin]

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? *

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.

 

[KLin]

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.

Much shorter and better solution. 😀

 

[chipy]

Much shorter and better solution. 😀

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!

 

[KLin]

Yea, strPrice never was being used in his code, so it was fine to take it out. 🙂