Go Back   AnandTech Forums > Software > Programming

Forums
· Hardware and Technology
· CPUs and Overclocking
· Motherboards
· Video Cards and Graphics
· Memory and Storage
· Power Supplies
· Cases & Cooling
· SFF, Notebooks, Pre-Built/Barebones PCs
· Networking
· Peripherals
· General Hardware
· Highly Technical
· Computer Help
· Home Theater PCs
· Consumer Electronics
· Digital and Video Cameras
· Mobile Devices & Gadgets
· Audio/Video & Home Theater
· Software
· Software for Windows
· All Things Apple
· *nix Software
· Operating Systems
· Programming
· PC Gaming
· Console Gaming
· Distributed Computing
· Security
· Social
· Off Topic
· Politics and News
· Discussion Club
· Love and Relationships
· The Garage
· Health and Fitness
· Merchandise and Shopping
· For Sale/Trade
· Hot Deals with Free Stuff/Contests
· Black Friday 2013
· Forum Issues
· Technical Forum Issues
· Personal Forum Issues
· Suggestion Box
· Moderator Resources
· Moderator Discussions
   

Reply
 
Thread Tools
Old 02-16-2010, 05:33 PM   #1
chipy
Golden Member
 
chipy's Avatar
 
Join Date: Feb 2003
Posts: 1,465
Default Excel VBA - extracting characters from cell

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!
__________________
Let there be light

betternatethanlever.com - longest joke in the world

ressikanflute.com
chipy is offline   Reply With Quote
Old 02-17-2010, 09:40 AM   #2
KLin
Lifer
 
KLin's Avatar
 
Join Date: Feb 2000
Location: Phreaznaux
Posts: 28,633
Default

Are there decimals places in the prices?
__________________
'L_'
KLin is offline   Reply With Quote
Old 02-17-2010, 09:57 AM   #3
KLin
Lifer
 
KLin's Avatar
 
Join Date: Feb 2000
Location: Phreaznaux
Posts: 28,633
Default

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.
__________________
'L_'
KLin is offline   Reply With Quote
Old 02-17-2010, 08:02 PM   #4
PowerEngineer
Platinum Member
 
PowerEngineer's Avatar
 
Join Date: Oct 2001
Posts: 2,461
Default

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...
PowerEngineer is offline   Reply With Quote
Old 02-17-2010, 11:49 PM   #5
chipy
Golden Member
 
chipy's Avatar
 
Join Date: Feb 2003
Posts: 1,465
Default

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.
__________________
Let there be light

betternatethanlever.com - longest joke in the world

ressikanflute.com

Last edited by chipy; 02-17-2010 at 11:57 PM.
chipy is offline   Reply With Quote
Old 02-18-2010, 12:59 AM   #6
PowerEngineer
Platinum Member
 
PowerEngineer's Avatar
 
Join Date: Oct 2001
Posts: 2,461
Default

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.
PowerEngineer is offline   Reply With Quote
Old 02-18-2010, 08:43 PM   #7
chipy
Golden Member
 
chipy's Avatar
 
Join Date: Feb 2003
Posts: 1,465
Default

Quote:
Originally Posted by PowerEngineer View Post
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? *
__________________
Let there be light

betternatethanlever.com - longest joke in the world

ressikanflute.com

Last edited by chipy; 02-18-2010 at 08:46 PM.
chipy is offline   Reply With Quote
Old 02-19-2010, 02:36 AM   #8
KLin
Lifer
 
KLin's Avatar
 
Join Date: Feb 2000
Location: Phreaznaux
Posts: 28,633
Default

Quote:
Originally Posted by chipy View Post
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.
__________________
'L_'
KLin is offline   Reply With Quote
Old 02-19-2010, 02:28 PM   #9
KLin
Lifer
 
KLin's Avatar
 
Join Date: Feb 2000
Location: Phreaznaux
Posts: 28,633
Default

Quote:
Originally Posted by PowerEngineer View Post
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.
__________________
'L_'
KLin is offline   Reply With Quote
Old 02-19-2010, 05:36 PM   #10
chipy
Golden Member
 
chipy's Avatar
 
Join Date: Feb 2003
Posts: 1,465
Default

Quote:
Originally Posted by KLin View Post
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!
__________________
Let there be light

betternatethanlever.com - longest joke in the world

ressikanflute.com
chipy is offline   Reply With Quote
Old 02-19-2010, 11:17 PM   #11
KLin
Lifer
 
KLin's Avatar
 
Join Date: Feb 2000
Location: Phreaznaux
Posts: 28,633
Default

Yea, strPrice never was being used in his code, so it was fine to take it out.
__________________
'L_'
KLin is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 07:13 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.