Sign in to follow this  
Followers 0
jando123

Convert a price from excel doc to an integer?

6 posts in this topic

I have an excel sheet with prices formatted as '$xx.xx'

I want to be able to convert the price to an actual integer that I can work with.

After reading the cell into a variable I tried using the Number() function.

I tried testing it by adding a number to the variable eg. $excelprice + 1 but I get 1 as the answer (if $excelprice=$9.99) instead of 10.99.

Any help? Thanks

Share this post


Link to post
Share on other sites



You will probably need to $newvar = StringTrimLeft($var,1)

then you could Int($newvar)

but you might not need that, I just get in the habit of defining integers as the program I use every day assumes everything is a string unless you tell it otherwise.


010101000110100001101001011100110010000001101001011100110010000

001101101011110010010000001110011011010010110011100100001

My Android cat and mouse game
https://play.google.com/store/apps/details?id=com.KaosVisions.WhiskersNSqueek

We're gonna need another Timmy!

Share this post


Link to post
Share on other sites

Thanks. I had to offset by 2 instead of 1 to get rid of the $ sign in the price but it worked

AutoIt seems to read the cell as a string. The excel doc has the prices formatted that way and I think it confuses AutoIt into thinking its a string, which necessitates the conversion.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Hi, jando123. This seems to work just fine

$excelprice = "9.95"
$var = Number($excelprice) + 1
MsgBox(0, "", $var)

Edit: I hate the auto page refresh :) I have to start refreshing manually before I post.

Edited by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Hey JLogan3o13,

I thinks its the dollar sign in the price that messes it up. The string trim function helped.

Edited by jando123

Share this post


Link to post
Share on other sites

Just throwing in my 2c.

As far as I remember it depends on where you are, in other words the locales in your Windows system versus Locale(s) in your MS Office system. You may get any combination of . or , and currency. All these variables (such as currency separator) can be read from excel programmatically. These are properties under the Application object. See some examples here.

And to make matters more interesting, you can read a cell through VBA in different ways:

  • Text: read what's parsed, basically what the user sees
  • Value : read the actual value, example: 1239 + 45/100 = 1239.45 OR 1239,45 depending on locale
  • Formula: read the formula behind, if any. Example "=1238.45 + 1"
So making such code bullet proof is not easy, you should at least consider understanding the differences between locales that their effect.

I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0