DocAElstein
02-17-2018, 06:02 PM
Excel VBA comma point thousand decimal separator number problem.
Hi,
This Tip is an answer and partial solution to my first Excel Forum Question a few years ago.
https://www.mrexcel.com/forum/questions-other-languages/780007-excel-vba-komma-punkt-tausend-zahl-problem.html
I have been using it myself in the meantime for over a year and have found it very useful, so I thought I would share it for the benefit of others. Also, as I expect that it is not a very efficient code, I would welcome any comments or improved or more efficient versions if anyone has any.
( If anyone can give a better solution , then that would be very interesting. I am sure there must be a much better way. )
( No combination of internal settings has ever consistently solved this problem )
I expect this solution only to be useful to people who like me are not doing particularly precise calculations but who are continually annoyed by Excel interchanging the comma , and point . as decimal and Thousand separators.
In this and the next Post I explain in detail the solution with an extended code. In the over next post is a shortened simplified version )
The problem Brief details
As Simple Example: I want things like on the left hand column to become, or to be recognised , as that on the right hand column ( with the . as the decimal separator )
What I Might HaveWhat I want
”Before””After”
001,4561.456( This number is almost one and a half )
1.000071.00007( This number is slightly more than one (It is approximately one) )
123,456.2123456.2( This number has a fractional part of 2/10 ( 0.2 ) )
0023.345,023345( This number is 23 Thousand 3 Hundred and forty-five )
-0023.345,0-23345( This number is minus 23 Thousand 3 Hundred and forty-five (It is a negative number) )[/td]
1.0071.007
1.34561.3456
1,23451.2345
01,07000001.07
1.34561.3456
1,23451.2345
.23450.2345
,45670.4567
-,340-0.34
00.040.04
-0,56000000-0.56
-,56000001-0.56000001
Note: If you attempt to copy those numbers from the left hand column into a spreadsheet you may experience the sort of problems that I often experience: You may notice that many of the formats will change : (Adjusting various internal Excel settings will effect different numbers differently) . This is how those numbers come out when I paste into a spreadsheet. As you can see, some numbers on the left hand column are clearly not representing the numbers that I want
MessedUpNumberFormatCommaPoint.jpg https://imgur.com/D1BSwAa
1962
So I would apply my solution to those numbers before I pasted them in : They may appear then in different formats depending on Excel internal settings, but they are being held in the magnitude that I wish. (In my particular case my Excel spreadsheet is using the point . as decimal separator )
More detail to problem
The problem arises because I use both English and German Excel and also handle data, ( for example copied form Internet sources or different types of data files ) , that may come from, or be wanted to be outputted in, the conventions of either language. It has often proved to be a nightmare to keep track of whether a comma , or point . is being used or wanted finally as decimal and Thousand separators.
So the end effect is that a number may be being recognised as a magnitude other than I want. As a simple example, say this number, 1.00 , is intended by me to represent the number One. It may arise that Excel will at some point choose to consider the . as a thousand separator and recognise the number as representing One Hundred.
No combination of internal settings has ever consistently solved this problem
The main issue that this solution attempt to address is that when a number is typed or pasted into a cell , then it will be taken as the number of the correct magnitude regardless of how it looks ( with some restrictions**) . Actually the point is that a “number” can be taken by or given to my solution so it is not really limited to what is typed into a cell. In fact I often use this solution in various parts of a code to consider a number at any point and check that it is being recognised as the correct magnitude number
What the solution does ( What the code I present does ) ( or effect of )
The final result is to have a number held in a variable of Double type which is a number of the correct magnitude I want, that is to say (regardless of how it may “look” in any situation, ( whether it has a comma or point or neither in it) ) , it is recognised by further coding as the number that I want. The initial requirement is that the number “looks” like a number with certain restrictions ** as explained in the next section
** This solution is limited:
It does not work on scientific number format.
It assumes that looking from the right of a number any first comma or point “seen” is the decimal separator.
A Number such as 123,456 or 123.456 will be returned as whole part 123 and decimal fraction part of 456 ( If that is not required then the workaround is to ensure that the number should be given in this form 123,456.0 or 123.456,0 or 123.456,00000 etc. )
This is how it works
Summary
A Number given is initially considered as a string. It is split into two parts, that before, and that after the last separator. The separator used is the first separator looking from the right.
The before part is then treated as a whole number. As such it has exactly the same magnitude and meaning as it did in the original Number
The part after is similarly treated as a number, but is then divided by some magnitude of 10 mathematically to give the magnitude as a decimal. This part then also represents the same magnitude that it did in the original Number.
Example: A number of 10.45 is given or taken in As String
____”10.45” ‘ Taken/ held as string
___= ”10.45” ‘ Is String variable
___ =”10”
___ & “45” ‘ Considered as two string variables
_ = 10 _+_ (45/100)
_ = 10 _+_ .45 ‘ The two string variables are converted to the numbers they represent, and then added mathematically
__ = 10.45
This is what the solution does briefly
A number is “taken in” / considered by us, As a String. It must “look” like a number and not be in scientific format.
It is treated initially as a string.
It does not matter if there are leading or trailing 0s , but they will not be returned
It can be given in any recognised form, like: _ .23 _ -0,34 _ -00089 _ -.23000000 _ 002340 _ 123,456,5.9806 _ etc.. etc..
This is how it works
Full Description
I am doing this with a Function code. Here is the Full version:
Function CStrSepDbl : http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10502#post10502
https://pastebin.com/1kq6h9Bn
I go on to explain that now in detail, so it is probably best to copy that to a code module and follow through it as you read the explanations.
Pseudo Declaration Function signature line
The first line of our Function code says a lot about what we are actually doing. This will be explained again later. But briefly for now: This is approximately our Signature line: It pseudo “Declares” what I am doing:
Function CStrSepDbl(strNumber As String) As Double
This is very similar to a simple variable declaration , Diming . So pseudo I have:
_ Dim __ CStrSepDbl( _¬¬¬ ) ______ As Double
or
_ Dim VariableFunctionCStrSepDbl( _ ) As Double
In fact VBA is holding the CStrSepDbl as just that: variable CStrSepDbl is a Double type variable, held in memory somewhere, god knows where, probably, I don’t know.
The only difference is that in code lines like_..
_Dim Retun As Double
___ Retun = CStrSepDbl()
_.. what will usually happen in the last code line above is that the Function code will be carried out before that code line is completed. The completion of the code line then does pseudo
Retun = current value held by the VBA variable CStrSepDbl
If the Function code does not do anything to the VBA variable CStrSepDbl , then Retun will become 0. ( 0 is the default value for a non filled Double variable ).
But we want the function to function such as to put the final number in the correct format into Retun, using a code line such as
__ Retun = CStrSepDbl(HereTheStringNumber)
The purposed of the Function code will be to arrange for that to happen….. somehow…… So we want it to function in such a way.
2 things a lot of people probably know, but maybe did not realise
The key to how to do this is probably in a couple of things that Factually are, but that I have never seen clearly documented or explained, .. that being ..
_1: Once I have syntaxly correctly written a Function code (named in this case CStrSepDbl), then VBA recognises CStrSepDbl as a Function in any other code. Because of that we can do a code line of _...
__ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats)
_... in any code ( even in the function CStrSepDbl, in which case another copy of the code is made available to run. This is called recursion )
In passing that code line, the Function code is completed before the code line is completed. In other words the main code in which the code line _ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats) _ is in will pause in the middle of this code line whilst the Function code Function CStrSepDbl is carried out.
_2: However, importantly in addition, within the Function CStrSepDbl, the use of a code line like _..
__ CStrSepDbl = 9.3
_.. will result in assigning the value 9.3 into the VBA variable CStrSepDbl
( Such a code line will error in any other code ). This would have the effect that in completion of this Main code code line _...
_ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats) ,
_.... the value of 9.3 would be copied from the VBA variable CStrSepDbl into Retun. In other words Retun would become 9.3 at the completion of that main code code line.
This is because, the pseudo code of that line, as mentioned is
_ Retun = the VBA variable CStrSepDbl
Function code strategy
So the strategy is to write a code which “takes in” at the first ( signature ) line the string variable ( HereTheStringNumberInAnyOfTheIndicatedFormats ) and turns this into a number of the required magnitude, and then , for example , puts that in a variable, _ NumberInCorrectMagnitude _ , and then finally just before the End of the Function CStrSepDbl places this in the VBA variable CStrSepDbl . The latter can be done, for example , using within the function code and towards the End of the code such a code line
_ CStrSepDbl = NumberInCorrectMagnitude
So a walk through the code, Function CStrSepDbl
In next Post:
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
Hi,
This Tip is an answer and partial solution to my first Excel Forum Question a few years ago.
https://www.mrexcel.com/forum/questions-other-languages/780007-excel-vba-komma-punkt-tausend-zahl-problem.html
I have been using it myself in the meantime for over a year and have found it very useful, so I thought I would share it for the benefit of others. Also, as I expect that it is not a very efficient code, I would welcome any comments or improved or more efficient versions if anyone has any.
( If anyone can give a better solution , then that would be very interesting. I am sure there must be a much better way. )
( No combination of internal settings has ever consistently solved this problem )
I expect this solution only to be useful to people who like me are not doing particularly precise calculations but who are continually annoyed by Excel interchanging the comma , and point . as decimal and Thousand separators.
In this and the next Post I explain in detail the solution with an extended code. In the over next post is a shortened simplified version )
The problem Brief details
As Simple Example: I want things like on the left hand column to become, or to be recognised , as that on the right hand column ( with the . as the decimal separator )
What I Might HaveWhat I want
”Before””After”
001,4561.456( This number is almost one and a half )
1.000071.00007( This number is slightly more than one (It is approximately one) )
123,456.2123456.2( This number has a fractional part of 2/10 ( 0.2 ) )
0023.345,023345( This number is 23 Thousand 3 Hundred and forty-five )
-0023.345,0-23345( This number is minus 23 Thousand 3 Hundred and forty-five (It is a negative number) )[/td]
1.0071.007
1.34561.3456
1,23451.2345
01,07000001.07
1.34561.3456
1,23451.2345
.23450.2345
,45670.4567
-,340-0.34
00.040.04
-0,56000000-0.56
-,56000001-0.56000001
Note: If you attempt to copy those numbers from the left hand column into a spreadsheet you may experience the sort of problems that I often experience: You may notice that many of the formats will change : (Adjusting various internal Excel settings will effect different numbers differently) . This is how those numbers come out when I paste into a spreadsheet. As you can see, some numbers on the left hand column are clearly not representing the numbers that I want
MessedUpNumberFormatCommaPoint.jpg https://imgur.com/D1BSwAa
1962
So I would apply my solution to those numbers before I pasted them in : They may appear then in different formats depending on Excel internal settings, but they are being held in the magnitude that I wish. (In my particular case my Excel spreadsheet is using the point . as decimal separator )
More detail to problem
The problem arises because I use both English and German Excel and also handle data, ( for example copied form Internet sources or different types of data files ) , that may come from, or be wanted to be outputted in, the conventions of either language. It has often proved to be a nightmare to keep track of whether a comma , or point . is being used or wanted finally as decimal and Thousand separators.
So the end effect is that a number may be being recognised as a magnitude other than I want. As a simple example, say this number, 1.00 , is intended by me to represent the number One. It may arise that Excel will at some point choose to consider the . as a thousand separator and recognise the number as representing One Hundred.
No combination of internal settings has ever consistently solved this problem
The main issue that this solution attempt to address is that when a number is typed or pasted into a cell , then it will be taken as the number of the correct magnitude regardless of how it looks ( with some restrictions**) . Actually the point is that a “number” can be taken by or given to my solution so it is not really limited to what is typed into a cell. In fact I often use this solution in various parts of a code to consider a number at any point and check that it is being recognised as the correct magnitude number
What the solution does ( What the code I present does ) ( or effect of )
The final result is to have a number held in a variable of Double type which is a number of the correct magnitude I want, that is to say (regardless of how it may “look” in any situation, ( whether it has a comma or point or neither in it) ) , it is recognised by further coding as the number that I want. The initial requirement is that the number “looks” like a number with certain restrictions ** as explained in the next section
** This solution is limited:
It does not work on scientific number format.
It assumes that looking from the right of a number any first comma or point “seen” is the decimal separator.
A Number such as 123,456 or 123.456 will be returned as whole part 123 and decimal fraction part of 456 ( If that is not required then the workaround is to ensure that the number should be given in this form 123,456.0 or 123.456,0 or 123.456,00000 etc. )
This is how it works
Summary
A Number given is initially considered as a string. It is split into two parts, that before, and that after the last separator. The separator used is the first separator looking from the right.
The before part is then treated as a whole number. As such it has exactly the same magnitude and meaning as it did in the original Number
The part after is similarly treated as a number, but is then divided by some magnitude of 10 mathematically to give the magnitude as a decimal. This part then also represents the same magnitude that it did in the original Number.
Example: A number of 10.45 is given or taken in As String
____”10.45” ‘ Taken/ held as string
___= ”10.45” ‘ Is String variable
___ =”10”
___ & “45” ‘ Considered as two string variables
_ = 10 _+_ (45/100)
_ = 10 _+_ .45 ‘ The two string variables are converted to the numbers they represent, and then added mathematically
__ = 10.45
This is what the solution does briefly
A number is “taken in” / considered by us, As a String. It must “look” like a number and not be in scientific format.
It is treated initially as a string.
It does not matter if there are leading or trailing 0s , but they will not be returned
It can be given in any recognised form, like: _ .23 _ -0,34 _ -00089 _ -.23000000 _ 002340 _ 123,456,5.9806 _ etc.. etc..
This is how it works
Full Description
I am doing this with a Function code. Here is the Full version:
Function CStrSepDbl : http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10502#post10502
https://pastebin.com/1kq6h9Bn
I go on to explain that now in detail, so it is probably best to copy that to a code module and follow through it as you read the explanations.
Pseudo Declaration Function signature line
The first line of our Function code says a lot about what we are actually doing. This will be explained again later. But briefly for now: This is approximately our Signature line: It pseudo “Declares” what I am doing:
Function CStrSepDbl(strNumber As String) As Double
This is very similar to a simple variable declaration , Diming . So pseudo I have:
_ Dim __ CStrSepDbl( _¬¬¬ ) ______ As Double
or
_ Dim VariableFunctionCStrSepDbl( _ ) As Double
In fact VBA is holding the CStrSepDbl as just that: variable CStrSepDbl is a Double type variable, held in memory somewhere, god knows where, probably, I don’t know.
The only difference is that in code lines like_..
_Dim Retun As Double
___ Retun = CStrSepDbl()
_.. what will usually happen in the last code line above is that the Function code will be carried out before that code line is completed. The completion of the code line then does pseudo
Retun = current value held by the VBA variable CStrSepDbl
If the Function code does not do anything to the VBA variable CStrSepDbl , then Retun will become 0. ( 0 is the default value for a non filled Double variable ).
But we want the function to function such as to put the final number in the correct format into Retun, using a code line such as
__ Retun = CStrSepDbl(HereTheStringNumber)
The purposed of the Function code will be to arrange for that to happen….. somehow…… So we want it to function in such a way.
2 things a lot of people probably know, but maybe did not realise
The key to how to do this is probably in a couple of things that Factually are, but that I have never seen clearly documented or explained, .. that being ..
_1: Once I have syntaxly correctly written a Function code (named in this case CStrSepDbl), then VBA recognises CStrSepDbl as a Function in any other code. Because of that we can do a code line of _...
__ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats)
_... in any code ( even in the function CStrSepDbl, in which case another copy of the code is made available to run. This is called recursion )
In passing that code line, the Function code is completed before the code line is completed. In other words the main code in which the code line _ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats) _ is in will pause in the middle of this code line whilst the Function code Function CStrSepDbl is carried out.
_2: However, importantly in addition, within the Function CStrSepDbl, the use of a code line like _..
__ CStrSepDbl = 9.3
_.. will result in assigning the value 9.3 into the VBA variable CStrSepDbl
( Such a code line will error in any other code ). This would have the effect that in completion of this Main code code line _...
_ Retun = CStrSepDbl(HereTheStringNumberInAnyOfTheIndicatedF ormats) ,
_.... the value of 9.3 would be copied from the VBA variable CStrSepDbl into Retun. In other words Retun would become 9.3 at the completion of that main code code line.
This is because, the pseudo code of that line, as mentioned is
_ Retun = the VBA variable CStrSepDbl
Function code strategy
So the strategy is to write a code which “takes in” at the first ( signature ) line the string variable ( HereTheStringNumberInAnyOfTheIndicatedFormats ) and turns this into a number of the required magnitude, and then , for example , puts that in a variable, _ NumberInCorrectMagnitude _ , and then finally just before the End of the Function CStrSepDbl places this in the VBA variable CStrSepDbl . The latter can be done, for example , using within the function code and towards the End of the code such a code line
_ CStrSepDbl = NumberInCorrectMagnitude
So a walk through the code, Function CStrSepDbl
In next Post:
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)