If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Validation of UK VAT Number
Gents,
Wow, didn't believe there could be so much on this subject. I have lots to try and see what fits best, once again thank you all very much. Rob "Rob" wrote in message ... Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. |
#12
|
|||
|
|||
Validation of UK VAT Number
Rob
Whilst this has been a very interesting discussion from an XL viewpoint I can't understand why you need to do this. Can't you just telephone the HMRC helpline? They will advise if the numbers are valid. I'm a little concerned that if this is the way these numbers are created/validated, is this not sensitive information that, if you are privy to it, should not be divulged to the world? If this is public knowledge then so be it. I can't see anywhere that says that it is though. Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Rob" wrote: Gents, Wow, didn't believe there could be so much on this subject. I have lots to try and see what fits best, once again thank you all very much. Rob "Rob" wrote in message ... Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. . |
#13
|
|||
|
|||
Validation of UK VAT Number
On Fri, 16 Apr 2010 21:51:59 +0100, "Rob"
wrote: Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. For just doing what you write: =IF(AND(LEN(SUBSTITUTE(A1," ",""))=9,OR(RIGHT(A1,2) = RIGHT(SUMPRODUCT({8;7;6;5;4;3;2},--MID(SUBSTITUTE( A1," ",""),{1;2;3;4;5;6;7},1))-97*{1,2,3,4},2))),"Valid","Not Valid") The formula first Makes sure there are only nine digits after removing any spaces Does the required multiplication on the first seven digits. Subtracts 97 * 1,2,3,4 and checks if any of those results are the same as the last two digits in the VAT. Note that even if the VAT were 999 999 999, the value of 9*8+9*7+9*6 .... is 315, so we never have to subtract more than 97*4 Also note that if you are entering the numbers, and there is a leading zero, you must enter the number as a string, either by pre-formatting the cell as TEXT, or preceding your entry with a single quote. --ron |
#14
|
|||
|
|||
Validation of UK VAT Number
Thanks Ron, another brilliant way to achieve the result.
Ta, Rob "Ron Rosenfeld" wrote in message ... On Fri, 16 Apr 2010 21:51:59 +0100, "Rob" wrote: Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. For just doing what you write: =IF(AND(LEN(SUBSTITUTE(A1," ",""))=9,OR(RIGHT(A1,2) = RIGHT(SUMPRODUCT({8;7;6;5;4;3;2},--MID(SUBSTITUTE( A1," ",""),{1;2;3;4;5;6;7},1))-97*{1,2,3,4},2))),"Valid","Not Valid") The formula first Makes sure there are only nine digits after removing any spaces Does the required multiplication on the first seven digits. Subtracts 97 * 1,2,3,4 and checks if any of those results are the same as the last two digits in the VAT. Note that even if the VAT were 999 999 999, the value of 9*8+9*7+9*6 ... is 315, so we never have to subtract more than 97*4 Also note that if you are entering the numbers, and there is a leading zero, you must enter the number as a string, either by pre-formatting the cell as TEXT, or preceding your entry with a single quote. --ron |
#15
|
|||
|
|||
Validation of UK VAT Number
It would appear that it is publicly accessable information:
http://www.advsofteng.com/vatid.html that's where I picked up some information regarding their content and format in addition to what Rob provided initially, and there's an on-line service to validate them also http://ec.europa.eu/taxation_customs/vies/vieshome.do "Russell Dawson" wrote: Rob Whilst this has been a very interesting discussion from an XL viewpoint I can't understand why you need to do this. Can't you just telephone the HMRC helpline? They will advise if the numbers are valid. I'm a little concerned that if this is the way these numbers are created/validated, is this not sensitive information that, if you are privy to it, should not be divulged to the world? If this is public knowledge then so be it. I can't see anywhere that says that it is though. Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Rob" wrote: Gents, Wow, didn't believe there could be so much on this subject. I have lots to try and see what fits best, once again thank you all very much. Rob "Rob" wrote in message ... Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. . |
#16
|
|||
|
|||
Validation of UK VAT Number
On Sat, 17 Apr 2010 21:04:04 +0100, "Rob"
wrote: Thanks Ron, another brilliant way to achieve the result. Ta, Rob Thanks for the feedback. Of course, it depends on the input being exactly 9 digits, allowing any number of space's to be interspersed. If the input is different, then some modification would be required. --ron |
#17
|
|||
|
|||
Validation of UK VAT Number
That's fine. It tells you the format i.e. 9 or 12 digits and the 3-4-2
format is no secret. It couldn't be as all businesses have to display it in invoices etc. What it doesn't give is the series of calculations apparently used to create what might appear to be a VAT number to anybody duped into accepting that number, first by it's issue and then confirmed as being valid via the sites you mentioned. I suspect that the validation site would only be using the appropriate calculation to validate the number as a possible VAT number because it passes the arithmetical test rather than check if the number has been issued and therefore a true VAT registration number. I'd be interested to know why Rob needs to do this. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "JLatham" wrote: It would appear that it is publicly accessable information: http://www.advsofteng.com/vatid.html that's where I picked up some information regarding their content and format in addition to what Rob provided initially, and there's an on-line service to validate them also http://ec.europa.eu/taxation_customs/vies/vieshome.do "Russell Dawson" wrote: Rob Whilst this has been a very interesting discussion from an XL viewpoint I can't understand why you need to do this. Can't you just telephone the HMRC helpline? They will advise if the numbers are valid. I'm a little concerned that if this is the way these numbers are created/validated, is this not sensitive information that, if you are privy to it, should not be divulged to the world? If this is public knowledge then so be it. I can't see anywhere that says that it is though. Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Rob" wrote: Gents, Wow, didn't believe there could be so much on this subject. I have lots to try and see what fits best, once again thank you all very much. Rob "Rob" wrote in message ... Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. . |
#18
|
|||
|
|||
Validation of UK VAT Number
On Sat, 17 Apr 2010 21:04:04 +0100, "Rob"
wrote: Thanks Ron, another brilliant way to achieve the result. Ta, Rob Here's another way: =IF(AND(LEN(SUBSTITUTE(A1," ",""))=9,MOD(SUMPRODUCT( --MID(SUBSTITUTE(A1," ",""),{1,2,3,4,5,6,7,8,9},1), {8,7,6,5,4,3,2,10,1}),97)=0),"Valid","Invalid") --ron |
#19
|
|||
|
|||
Validation of UK VAT Number
On Sat, 17 Apr 2010 23:39:01 -0700, Russell Dawson
wrote: That's fine. It tells you the format i.e. 9 or 12 digits and the 3-4-2 format is no secret. It couldn't be as all businesses have to display it in invoices etc. What it doesn't give is the series of calculations apparently used to create what might appear to be a VAT number to anybody duped into accepting that number, first by it's issue and then confirmed as being valid via the sites you mentioned. I suspect that the validation site would only be using the appropriate calculation to validate the number as a possible VAT number because it passes the arithmetical test rather than check if the number has been issued and therefore a true VAT registration number. I'd be interested to know why Rob needs to do this. The Modulus 97 algorithm is not secret. See http://sima.cat/nif.php But I don't know if the new modulus 9755 algorithm, which is supposed to be being implemented in GB this year to increase the range of allowable VAT numbers, is secret or not. Certainly it can be obtained with a legitimate business reason. --ron |
#20
|
|||
|
|||
Validation of UK VAT Number
Can I join this free-for-all?
Here's my contribution that I forgot to send yesterday 1) returns FALSE/TRUE =MOD(SUMPRODUCT(VALUE(MID(SUBSTITUTE(A2," ",""),{1;2;3;4;5;6;7},1)),{8;7;6;5;4;3;2}),97)-97=-RIGHT(A2,2) 2) returns text of choice =IF(=MOD(SUMPRODUCT(VALUE(MID(SUBSTITUTE(A2," ",""),{1;2;3;4;5;6;7},1)),{8;7;6;5;4;3;2}),97)-97=-RIGHT(A2,2),"Valid", "Invalid") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Rob" wrote in message ... Hi, I want to check a number of VAT numbers using a formula, the first check is to ensure the number is 9 digits (may need to remove spaces to ensure clean data). The process is then to apply the below criteria which has proved difficult for he to accomplish. Any help will be most welcome. Thanks, Rob 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. Example: VAT registration number 339 0727 47 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. 1.. The first seven digits of the VAT registration number are listed vertically. 2.. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3.. The sum of the multiplications is calculated. 4.. 97 is subtracted from the sum as many times as is necessary to arrive at a negative number. 5.. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. |
Thread Tools | |
Display Modes | |
|
|