Jump to content

Convert a price from excel doc to an integer?


Recommended Posts

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

Link to comment
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!

Link to comment
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.

Link to comment
Share on other sites

  • Moderators

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
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.

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...