# 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

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

We're gonna need another Timmy!

##### 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 on other sites

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

##### Share on other sites

Hey JLogan3o13,

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

Edited by jando123

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

## Create an account

Register a new account

• ### Recently Browsing   0 members

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...