Hello its the noob again ,
I can't find how to use number variables, for example I have a variable Tax included from an excel document that look like that : 829,00 €
I want to make a variable called taxincl with the value 829 (only numerical without the euro sign) :
taxincl = copied value
I want to calculate the price before VAT 20% which is taxincl / 1,2 and make a variable with that value called beforetax :
beforetax = taxincl / 1,2
I want to calculate the VAT which = taxincl - beforetax make a variable called vat with that value :
vat = taxincl - beforetax
Once I will have my 3 numerical variables, taxincl, beforetax and vat, I will be able to paste them in a document on the net but I don't understand how you make numerical variables in KM, thx for you help
Here's a quick example to get you started:
Example Macro.kmmacros (1.9 KB)
I think my very first post on this website (about a decade ago) was my confusion between numerical values and string values in KM. To this day, I still feel bad that I got it wrong. Maybe the fact that you're here is a divine coincidence allowing me to make up for my initial mistake, by trying to give you the correct answer this time, instead of giving someone a wrong answer, which I gave someone a decade ago. So thanks for the opportunity to clear my conscience. I should be able to sleep better now.
The right answer that I should have given is that all variables in KM are strings. Unlike most compiled languages which usually store numbers as "a series of binary bits', all KM values are stored as ASCII strings, and there are no equivalents to "longint" or any such thing. When you see a KM action like "Set Variable to Calculation" all this means is that KM is going to interpret the KM variables as "string numbers", rather than as strings.
It seems to me that your post has the following different questions:
- How do I remove currency strings to make my data into a number that can work with calculation actions?
- How do I do arithmetic on KM variables that contain numbers?
- How do I paste the content of KM variables onto a web page?
Have I got the questions correct? There are multiple possible answers to each of your questions. The answer from GG appears to be focussed on question #2.
I wish to note that your question implies that you are working with European numerical syntax (eg, commas instead of decimals for fractions) and so there may be an extra step for you to convert you strings from comma-delimited fractions to decimal-delimited fractions. At this point I haven't even looked up if KM supports the comma symbol as a decimal separator. This is really your fourth question, I guess.
Thank you very much @gglick for your answer.
In fact I already had tried something similar but I didn't understand why it was not working. Then I tried your macro and it is still not working for me. So I was very glad to see @Sleepy's answer, and to understand that the issue resides in his number 1 question, how do I make my data into a variable that works with a KM calculation?
So as the data is a copy from an Excel cell formatted with Euros and European syntax (commas), I only know the "trim white space" command which I use all the time (I use Excel a lot) to remove the extra spaces at the end of the copied data. I need to find out how I can remove the € sign and how I can transform the comma into a point.
I know how to answer question 2 now thanks to @gglick, still I don't understand what the format line # ##0.00 is for.
I have no problem with question 3 @Sleepy, I can do that myself.
Thank you very much for your help
Those questions are fairly easy to answer, but there are several ways to do those things. I'm not sure which way is best for you. One possible way to replace a comma with a period is:
And you could even use that same way to "replace a Euro symbol with an empty string."
There are other ways to do these things. I usually use a different way, but these ways might be best for you.
Is that explanation clear? And do you have more questions?
I'm packing it in for the day now. TTYL. I guess I'm sleepy.
@Sleepy already covered ways you can massage the data copied from Excel into a pure numerical string format KM can use for calculations, so I'll just provide a link to the reference I used for formatting the calculation's result: UTS #35: Unicode Locale Data Markup Language
ty very much for your help @gglick and @Sleepy, ye it all work. Well it almost all work.. I'm coming across a very strange problem:
I have a variable called TTC and I ask the macro to
Set Variable “HT” To Calculation “(TTC-10)/1.021+10”
As long as TTC is less than 1000, it will do the calculation and it all work fine.
When TTC is more than 1000, it will send me an error message "failed to calculate"
I have no idea why.. Someone knows ? It is sad because fortunately some of my invoice are more than 1000
I can assure you there is no "limit of 1000". Most likely the string that you say is "more than 1000" actually contains additional characters that are not part of a normal number.
I think the easiest way to solve this is as follows... Turn off the flag "Failure aborts macro" on the"Set Variable to Calculation" and then follow that statement immediately with this action. What this will do is display the characters in a window so you can see what the problem was. Of course, use your own variable name where it says "MyVariableName".
If this doesn't solve your problem, we will still find the answer. But I think there's a 90% chance this will show you your problem. The reason I inserted the "-->" keys was so that you wouldn't fail to see certain kinds of extraneous characters like spaces or carriage returns. So do it that way.
ty again YES @Sleepy it was something wrong with my variable when I copy from my excel format, if it's more than a thousand there is a space added between the 1 and the rest of the number like
1 440 and not 1440
I didn't manage to get rid of that space because search and replace doesn't find just a space I tried also a 1 + a space.. doesn't work neither..
I tried filtering with "trim white space" but that only work with the spaces before or after not the spaces in the middle
So, do someone have an idea of how I could get rid of the space in the middle of my number?
Not sure why search and replace isn't finding the space for you, but using a regular expression with
\s should get rid of any white space characters in the variable:
YES !!!! Everything works now with the \s ! tytytyty