|
Validation of UK VAT Number
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. |
Validation of UK VAT Number
Is the VAT number *always* a 9 digit string (excluding any internal spaces)?
-- Biff Microsoft Excel MVP "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. |
Validation of UK VAT Number
Rob
I put the following together to achieve exactly what you are asking: Cells A5 thro' to A11 - enter labels: 1st No., 2nd No., 3rd No. and so on to 7th No. Cell A13 - enter label: Last 2 Nos. Put Boxes around cells B5 to B11 and B13 Cell B13 goto data validation and enter formula: =AND(B130,B13=D13*-1) and format to a colour (yellow?) Add second condition: =D13<0 and format to a colour (red?) OK Now in Cell D5 enter =B5*8 Cell D6 enter =B6*7 Cell D7 enter =B7*6 Cell D8 enter =B8*5 Cell D9 enter =B9*4 Cell D10 enter = B10*3 Cell D11 enter = B11*2 Cell D12 enter =SUM(D5:D11) Cell D13 enter =IF(B13="",0,IF(D12-97<0,D12-97,IF(D12-97-97<0,D12-97-97,D12-97-97-97))) Either hide column D or format D5:D13 white to hide calculation. Then in Cell B13 go to Data Validation, Settings tab and enter: Allow: Whole Number - tick Ignore Blank Data: equal to Value: =D13*-1 Error Alert tab - tick Show alert after invalid data is entered Style: Stop Title: ERROR Error message: VAT Number is not valid OK You could then finish off the chart: Cell A1 "VAT NUMBER VALIDATION" Cell A2 "Enter VAT number below" Cell A3 "A valid number produces a yellow box" Now enter the VAT number to be tested, one digit in each of cells B5 thro' to B11 and the last two digits in B13. If the number is valid the cell B13 will turn yellow else if not valid the cell will turn red and a small ERROR window will appear telling you the VAT number is nit valid. Hope you will find this of some use, I do. I guess to be really sophisticated you could have a button to press (run a macro) to clear the entries ready for a new number, but I'll let somebody else tell you how to do that, if you don't know. "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. . |
Validation of UK VAT Number
Perhaps give this one a try also:
With the VAT number in A1, with or without spaces in B1 this formula =SUBSTITUTE(A1," ","") NOTE: A1 should be formatted as text which will also preserve leading zeros if there are any. Formulas for other cells: A2 =MID($B$1,1,1) * 8 A3 =MID($B$1,2,1) * 7 A4 =MID($B$1,3,1) * 6 A5 =MID($B$1,4,1) * 5 A6 =MID($B$1,5,1) * 4 A7 =MID($B$1,6,1) * 3 A8 =MID($B$1,7,1) * 2 Now, you can put these other formulas pretty much anywhere, as long as you reference the previous ones properly Total of the multiplication: =SUM(A2:A8) I put it into B8 Calculate down to negative number, although this shows it as positive =97-MOD(B8,97) I had this one in B9 and finally: =IF(RIGHT($B$1,2)*1=B9,"Valid VAT","Not Valid") Now, if you want to do away with all of the 'intermediate' steps, you can simply use A1 and B1 as indicated before, and put this formula somewhe =IF(RIGHT($B$1,2)*1=(97-MOD(SUM(MID($B$1,1,1) * 8,MID($B$1,2,1) * 7,MID($B$1,3,1) * 6,MID($B$1,4,1) * 5,MID($B$1,5,1) * 4,MID($B$1,6,1) * 3,MID($B$1,7,1) * 2),97)),"Valid VAT","Not Valid VAT") "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. . |
Validation of UK VAT Number
The previous formula will return errors if the number entered is less than 7
digits long, so to keep things neat looking while still providing valid/invalid indications: =IF(LEN($B$1)<9,"Not a Valid UK VAT",IF(RIGHT($B$1,2)*1=(97-MOD(SUM(MID($B$1,1,1) * 8,MID($B$1,2,1) * 7,MID($B$1,3,1) * 6,MID($B$1,4,1) * 5,MID($B$1,5,1) * 4,MID($B$1,6,1) * 3,MID($B$1,7,1) * 2),97)),"Valid VAT","Not A Valid UK VAT")) "JLatham" wrote: Perhaps give this one a try also: With the VAT number in A1, with or without spaces in B1 this formula =SUBSTITUTE(A1," ","") NOTE: A1 should be formatted as text which will also preserve leading zeros if there are any. Formulas for other cells: A2 =MID($B$1,1,1) * 8 A3 =MID($B$1,2,1) * 7 A4 =MID($B$1,3,1) * 6 A5 =MID($B$1,4,1) * 5 A6 =MID($B$1,5,1) * 4 A7 =MID($B$1,6,1) * 3 A8 =MID($B$1,7,1) * 2 Now, you can put these other formulas pretty much anywhere, as long as you reference the previous ones properly Total of the multiplication: =SUM(A2:A8) I put it into B8 Calculate down to negative number, although this shows it as positive =97-MOD(B8,97) I had this one in B9 and finally: =IF(RIGHT($B$1,2)*1=B9,"Valid VAT","Not Valid") Now, if you want to do away with all of the 'intermediate' steps, you can simply use A1 and B1 as indicated before, and put this formula somewhe =IF(RIGHT($B$1,2)*1=(97-MOD(SUM(MID($B$1,1,1) * 8,MID($B$1,2,1) * 7,MID($B$1,3,1) * 6,MID($B$1,4,1) * 5,MID($B$1,5,1) * 4,MID($B$1,6,1) * 3,MID($B$1,7,1) * 2),97)),"Valid VAT","Not Valid VAT") "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. . |
Validation of UK VAT Number
Biff,
According to this page it is either 9 or 12 digits in the UK. http://www.advsofteng.com/vatid.html I believe I've provided the 9-digit solution below (or at least one possible solution). I don't know the rules for 12-digit UK VAT numbers, but may try to find out just out of curiousity. Looks like we can validate any results we come up with on this page: http://ec.europa.eu/taxation_customs/vies/vieshome.do "T. Valko" wrote: Is the VAT number *always* a 9 digit string (excluding any internal spaces)? -- Biff Microsoft Excel MVP "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. . |
Validation of UK VAT Number
Further reading (mostly on that same referenced page) indicates that a
12-digit UK number has the last 3 digits indicating the sub-company of the main VAT holder. So they are probably not players in it at all. Also a full VAT 'number' in the UK would include "GB " at its start, and of course those would need to be eliminated as characters. It would seem that we want the 1st 9 numeric characters in the entry as the ones to work with, and the easiest thing to do is depend on the user to enter only the 9 digits of concern?? "T. Valko" wrote: Is the VAT number *always* a 9 digit string (excluding any internal spaces)? -- Biff Microsoft Excel MVP "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. . |
Validation of UK VAT Number
Here's what I came up with based on the single example of:
339 0727 47 =IF(COUNT(MATCH(-RIGHT(A1,2),INDEX(SUM(--MID(SUBSTITUTE(A1," ",""),{1,2,3,4,5,6,7},1)*{8,7,6,5,4,3,2})-(97*{1,2,3,4,5,6}),0),0)),"Valid","Invalid") If we need to validate the length do we need to include the spaces? Is the number format *always* 3 digits<space4digits<space2digits? It would be better if the OP could post *several* examples of both valid and invalid numbers so we can test more thoroughly. -- Biff Microsoft Excel MVP "JLatham" wrote in message ... Biff, According to this page it is either 9 or 12 digits in the UK. http://www.advsofteng.com/vatid.html I believe I've provided the 9-digit solution below (or at least one possible solution). I don't know the rules for 12-digit UK VAT numbers, but may try to find out just out of curiousity. Looks like we can validate any results we come up with on this page: http://ec.europa.eu/taxation_customs/vies/vieshome.do "T. Valko" wrote: Is the VAT number *always* a 9 digit string (excluding any internal spaces)? -- Biff Microsoft Excel MVP "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. . |
Validation of UK VAT Number
Rob,
Here's a UDF to accomplish the same thing, with even more versatility. A person not knowing that only 9 digits were of significance might include something like: GB 339 0727 47 or even GB 339 0727 47 001 as an input, which results in the worksheet formula failing. This UDF takes all of that into account and simply grabs the 1st 9 numeric characters entered, ignoring text, spaces and any extra digits entered. To use it on a worksheet, you'd enter it as =ValidateUKVAT("339 0727 47") or =ValidateUKVAT("GB 339072747") or even =ValidateUKVAT("GB339072747001") or just plain old =ValidateUKVAT("339072747") or if you let a person enter the VAT into a cell, say A1, then it could be in another cell as: =ValidateUKVAT(A1) And that would be handy if you had a whole list of VATs to verify on a sheet. To add the function to a workbook, open the workbook, press [Alt]+[F11] to open the VB editor and choose Insert -- Module. Then copy the code below into that module and close the VB Editor. Simply use the function in cells as demonstrated above from that point. Function ValidateUKVAT(initialEntry As String) As String 'by JLatham, Excel MVP 2006-2010 '16 APRIL 2010 ' 'UK VAT codes can take on 2 basic forms: ' GB 339072747 'and/or ' GB 339072747001 where the last 3 digits indicate a sub-company 'in either case, we ignore everything except the 'first 9 digits in the entry Const subValue = 97 Const vatDigitsCount = 9 Dim vatCodeOnly As String Dim LC As Integer ' loop counter Dim multipliers As Variant Dim checkSum As Integer Dim checkText As String multipliers = Array(8, 7, 6, 5, 4, 3, 2) initialEntry = Range("A1").Value If Len(initialEntry) < 9 Then ValidateUKVAT = "Not a valid UK VAT" Exit Function End If For LC = 1 To Len(initialEntry) If Mid(initialEntry, LC, 1) = "0" And _ Mid(initialEntry, LC, 1) <= "9" Then vatCodeOnly = vatCodeOnly & Mid(initialEntry, LC, 1) If Len(vatCodeOnly) = vatDigitsCount Then Exit For ' got 1st 9 digits End If End If Next ' end LC loop For LC = 1 To 7 checkSum = checkSum + Val(Mid(vatCodeOnly, LC, 1)) * multipliers(LC - 1) Next Do While checkSum 0 checkSum = checkSum - subValue Loop 'presumed there is the possibility that checksum could 'turn out to be a single digit negative value, so 'guard against that here checkText = Trim(Str(checkSum)) If Len(checkText) = 2 Then checkText = Replace(checkText, "-", "0") End If If Right(checkText, 2) = Right(vatCodeOnly, 2) Then ValidateUKVAT = "Is a valid UK VAT" Else ValidateUKVAT = "Not a valid UK VAT" End If End Function "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. . |
Validation of UK VAT Number
I like that, much better handling of all of the MID() entries that I had.
As near as I can tell from the EU/UK sites, the spaces are optional and may or may not appear in a number. I suspect the spaces, when used, are much like the dashes in telephone numbers or SSANs - more to give the reader an easy way to remember the number as a series of short groups than one 9 or 12 digit entry. I think that for all practical purposes, that your formula is good enough and all that would be needed is a notice to the user not to include the "GB " or sub-company identification. That is, enter 9 digits, with or without spaces. I think best-guess for dealing with spaces would be to assume that they don't enter any, and verify that it is at least 9 characters long to begin with and heaven help them if they enter anything other than 9 digits and somewhere between 0 and a zillion spaces. This all goes toward why I rather like the UDF - it pretty much eliminates concern over anything except that there are somehow or other (at least) 9 digits in the input. "T. Valko" wrote: Here's what I came up with based on the single example of: 339 0727 47 =IF(COUNT(MATCH(-RIGHT(A1,2),INDEX(SUM(--MID(SUBSTITUTE(A1," ",""),{1,2,3,4,5,6,7},1)*{8,7,6,5,4,3,2})-(97*{1,2,3,4,5,6}),0),0)),"Valid","Invalid") If we need to validate the length do we need to include the spaces? Is the number format *always* 3 digits<space4digits<space2digits? It would be better if the OP could post *several* examples of both valid and invalid numbers so we can test more thoroughly. -- Biff Microsoft Excel MVP "JLatham" wrote in message ... Biff, According to this page it is either 9 or 12 digits in the UK. http://www.advsofteng.com/vatid.html I believe I've provided the 9-digit solution below (or at least one possible solution). I don't know the rules for 12-digit UK VAT numbers, but may try to find out just out of curiousity. Looks like we can validate any results we come up with on this page: http://ec.europa.eu/taxation_customs/vies/vieshome.do "T. Valko" wrote: Is the VAT number *always* a 9 digit string (excluding any internal spaces)? -- Biff Microsoft Excel MVP "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. . . |
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. |
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. . |
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 |
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 |
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. . |
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 |
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. . |
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 |
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 |
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. |
Validation of UK VAT Number
Thanks Ron.
I'll sleep easy tonight. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Ron Rosenfeld" wrote: 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 . |
Validation of UK VAT Number
Okay, here is yet another approach for you to try.
If there are no spaces within the VAT number, then try this... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") If there could be spaces in the VAT number, and if they are in the positions shown when present, then use this formula instead... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""), ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") -- Rick (MVP - Excel) "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. |
Validation of UK VAT Number
Actually, stealing the comparison approach Bernard used (leave the negative
value negative and make the last two digits negative instead), we get these even shorter formulas... If there are no spaces.... =IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid") If there could be spaces in the VAT number, and if they are in the positions shown when present... =IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""), ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid") -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Okay, here is yet another approach for you to try. If there are no spaces within the VAT number, then try this... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") If there could be spaces in the VAT number, and if they are in the positions shown when present, then use this formula instead... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""), ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") -- Rick (MVP - Excel) "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. |
Actually, stealing the comparison approach Bernard used (leave the
does anyone know how to create a excel udf function which accesses the euopa vies soap wsdl to check these numbers?
On Friday, April 16, 2010 4:51 PM 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. On Friday, April 16, 2010 5:19 PM T. Valko wrote: Is the VAT number *always* a 9 digit string (excluding any internal spaces)? -- Biff Microsoft Excel MVP On Friday, April 16, 2010 7:00 PM Ro wrote: Rob I put the following together to achieve exactly what you are asking: Cells A5 thro' to A11 - enter labels: 1st No., 2nd No., 3rd No. and so on to 7th No. Cell A13 - enter label: Last 2 Nos. Put Boxes around cells B5 to B11 and B13 Cell B13 goto data validation and enter formula: =AND(B130,B13=D13*-1) and format to a colour (yellow?) Add second condition: =D13<0 and format to a colour (red?) OK Now in Cell D5 enter =B5*8 Cell D6 enter =B6*7 Cell D7 enter =B7*6 Cell D8 enter =B8*5 Cell D9 enter =B9*4 Cell D10 enter = B10*3 Cell D11 enter = B11*2 Cell D12 enter =SUM(D5:D11) Cell D13 enter =IF(B13="",0,IF(D12-97<0,D12-97,IF(D12-97-97<0,D12-97-97,D12-97-97-97))) Either hide column D or format D5:D13 white to hide calculation. Then in Cell B13 go to Data Validation, Settings tab and enter: Allow: Whole Number - tick Ignore Blank Data: equal to Value: =D13*-1 Error Alert tab - tick Show alert after invalid data is entered Style: Stop Title: ERROR Error message: VAT Number is not valid OK You could then finish off the chart: Cell A1 "VAT NUMBER VALIDATION" Cell A2 "Enter VAT number below" Cell A3 "A valid number produces a yellow box" Now enter the VAT number to be tested, one digit in each of cells B5 thro' to B11 and the last two digits in B13. If the number is valid the cell B13 will turn yellow else if not valid the cell will turn red and a small ERROR window will appear telling you the VAT number is nit valid. Hope you will find this of some use, I do. I guess to be really sophisticated you could have a button to press (run a macro) to clear the entries ready for a new number, but I will let somebody else tell you how to do that, if you do not know. "Rob" wrote: On Friday, April 16, 2010 9:00 PM JLatham wrote: Perhaps give this one a try also: With the VAT number in A1, with or without spaces in B1 this formula =SUBSTITUTE(A1," ","") NOTE: A1 should be formatted as text which will also preserve leading zeros if there are any. Formulas for other cells: A2 =MID($B$1,1,1) * 8 A3 =MID($B$1,2,1) * 7 A4 =MID($B$1,3,1) * 6 A5 =MID($B$1,4,1) * 5 A6 =MID($B$1,5,1) * 4 A7 =MID($B$1,6,1) * 3 A8 =MID($B$1,7,1) * 2 Now, you can put these other formulas pretty much anywhere, as long as you reference the previous ones properly Total of the multiplication: =SUM(A2:A8) I put it into B8 Calculate down to negative number, although this shows it as positive =97-MOD(B8,97) I had this one in B9 and finally: =IF(RIGHT($B$1,2)*1=B9,"Valid VAT","Not Valid") Now, if you want to do away with all of the 'intermediate' steps, you can simply use A1 and B1 as indicated before, and put this formula somewhe =IF(RIGHT($B$1,2)*1=(97-MOD(SUM(MID($B$1,1,1) * 8,MID($B$1,2,1) * 7,MID($B$1,3,1) * 6,MID($B$1,4,1) * 5,MID($B$1,5,1) * 4,MID($B$1,6,1) * 3,MID($B$1,7,1) * 2),97)),"Valid VAT","Not Valid VAT") "Rob" wrote: On Friday, April 16, 2010 9:07 PM JLatham wrote: The previous formula will return errors if the number entered is less than 7 digits long, so to keep things neat looking while still providing valid/invalid indications: =IF(LEN($B$1)<9,"Not a Valid UK VAT",IF(RIGHT($B$1,2)*1=(97-MOD(SUM(MID($B$1,1,1) * 8,MID($B$1,2,1) * 7,MID($B$1,3,1) * 6,MID($B$1,4,1) * 5,MID($B$1,5,1) * 4,MID($B$1,6,1) * 3,MID($B$1,7,1) * 2),97)),"Valid VAT","Not A Valid UK VAT")) "JLatham" wrote: On Friday, April 16, 2010 9:11 PM JLatham wrote: Biff, According to this page it is either 9 or 12 digits in the UK. http://www.advsofteng.com/vatid.html I believe I have provided the 9-digit solution below (or at least one possible solution). I do not know the rules for 12-digit UK VAT numbers, but may try to find out just out of curiousity. Looks like we can validate any results we come up with on this page: http://ec.europa.eu/taxation_customs/vies/vieshome.do "T. Valko" wrote: On Friday, April 16, 2010 9:20 PM JLatham wrote: Further reading (mostly on that same referenced page) indicates that a 12-digit UK number has the last 3 digits indicating the sub-company of the main VAT holder. So they are probably not players in it at all. Also a full VAT 'number' in the UK would include "GB " at its start, and of course those would need to be eliminated as characters. It would seem that we want the 1st 9 numeric characters in the entry as the ones to work with, and the easiest thing to do is depend on the user to enter only the 9 digits of concern?? "T. Valko" wrote: On Friday, April 16, 2010 9:56 PM T. Valko wrote: Here is what I came up with based on the single example of: 339 0727 47 =IF(COUNT(MATCH(-RIGHT(A1,2),INDEX(SUM(--MID(SUBSTITUTE(A1," ",""),{1,2,3,4,5,6,7},1)*{8,7,6,5,4,3,2})-(97*{1,2,3,4,5,6}),0),0)),"Valid","Invalid") If we need to validate the length do we need to include the spaces? Is the number format *always* 3 digits<space4digits<space2digits? It would be better if the OP could post *several* examples of both valid and invalid numbers so we can test more thoroughly. -- Biff Microsoft Excel MVP On Friday, April 16, 2010 10:01 PM JLatham wrote: Rob, Here is a UDF to accomplish the same thing, with even more versatility. A person not knowing that only 9 digits were of significance might include something like: GB 339 0727 47 or even GB 339 0727 47 001 as an input, which results in the worksheet formula failing. This UDF takes all of that into account and simply grabs the 1st 9 numeric characters entered, ignoring text, spaces and any extra digits entered. To use it on a worksheet, you would enter it as =ValidateUKVAT("339 0727 47") or =ValidateUKVAT("GB 339072747") or even =ValidateUKVAT("GB339072747001") or just plain old =ValidateUKVAT("339072747") or if you let a person enter the VAT into a cell, say A1, then it could be in another cell as: =ValidateUKVAT(A1) And that would be handy if you had a whole list of VATs to verify on a sheet. To add the function to a workbook, open the workbook, press [Alt]+[F11] to open the VB editor and choose Insert -- Module. Then copy the code below into that module and close the VB Editor. Simply use the function in cells as demonstrated above from that point. Function ValidateUKVAT(initialEntry As String) As String 'by JLatham, Excel MVP 2006-2010 '16 APRIL 2010 ' 'UK VAT codes can take on 2 basic forms: ' GB 339072747 'and/or ' GB 339072747001 where the last 3 digits indicate a sub-company 'in either case, we ignore everything except the 'first 9 digits in the entry Const subValue = 97 Const vatDigitsCount = 9 Dim vatCodeOnly As String Dim LC As Integer ' loop counter Dim multipliers As Variant Dim checkSum As Integer Dim checkText As String multipliers = Array(8, 7, 6, 5, 4, 3, 2) initialEntry = Range("A1").Value If Len(initialEntry) < 9 Then ValidateUKVAT = "Not a valid UK VAT" Exit Function End If For LC = 1 To Len(initialEntry) If Mid(initialEntry, LC, 1) = "0" And _ Mid(initialEntry, LC, 1) <= "9" Then vatCodeOnly = vatCodeOnly & Mid(initialEntry, LC, 1) If Len(vatCodeOnly) = vatDigitsCount Then Exit For ' got 1st 9 digits End If End If Next ' end LC loop For LC = 1 To 7 checkSum = checkSum + Val(Mid(vatCodeOnly, LC, 1)) * multipliers(LC - 1) Next Do While checkSum 0 checkSum = checkSum - subValue Loop 'presumed there is the possibility that checksum could 'turn out to be a single digit negative value, so 'guard against that here checkText = Trim(Str(checkSum)) If Len(checkText) = 2 Then checkText = Replace(checkText, "-", "0") End If If Right(checkText, 2) = Right(vatCodeOnly, 2) Then ValidateUKVAT = "Is a valid UK VAT" Else ValidateUKVAT = "Not a valid UK VAT" End If End Function "Rob" wrote: On Saturday, April 17, 2010 12:55 AM JLatham wrote: I like that, much better handling of all of the MID() entries that I had. As near as I can tell from the EU/UK sites, the spaces are optional and may or may not appear in a number. I suspect the spaces, when used, are much like the dashes in telephone numbers or SSANs - more to give the reader an easy way to remember the number as a series of short groups than one 9 or 12 digit entry. I think that for all practical purposes, that your formula is good enough and all that would be needed is a notice to the user not to include the "GB " or sub-company identification. That is, enter 9 digits, with or without spaces. I think best-guess for dealing with spaces would be to assume that they do not enter any, and verify that it is at least 9 characters long to begin with and heaven help them if they enter anything other than 9 digits and somewhere between 0 and a zillion spaces. This all goes toward why I rather like the UDF - it pretty much eliminates concern over anything except that there are somehow or other (at least) 9 digits in the input. "T. Valko" wrote: On Saturday, April 17, 2010 2:44 AM Rob wrote: Gents, Wow, did not 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 On Saturday, April 17, 2010 11:42 AM Russell Dawson wrote: Rob Whilst this has been a very interesting discussion from an XL viewpoint I cannot understand why you need to do this. Can't you just telephone the HMRC helpline? They will advise if the numbers are valid. I am 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 cannot see anywhere that says that it is though. Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Rob" wrote: On Saturday, April 17, 2010 3:28 PM Ron Rosenfeld wrote: wrote: 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 On Saturday, April 17, 2010 4:04 PM Rob wrote: Thanks Ron, another brilliant way to achieve the result. Ta, Rob On Saturday, April 17, 2010 4:24 PM JLatham wrote: It would appear that it is publicly accessable information: http://www.advsofteng.com/vatid.html that is where I picked up some information regarding their content and format in addition to what Rob provided initially, and there is an on-line service to validate them also http://ec.europa.eu/taxation_customs/vies/vieshome.do "Russell Dawson" wrote: On Saturday, April 17, 2010 7:26 PM Ron Rosenfeld wrote: wrote: 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 On Sunday, April 18, 2010 2:39 AM 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 could not be as all businesses have to display it in invoices etc. What it does not 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 is 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: On Sunday, April 18, 2010 7:09 AM Ron Rosenfeld wrote: wrote: Here is 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 On Sunday, April 18, 2010 7:20 AM Ron Rosenfeld wrote: The Modulus 97 algorithm is not secret. See http://sima.cat/nif.php But I do not 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 On Sunday, April 18, 2010 9:03 AM Bernard Liengme wrote: Can I join this free-for-all? Here is 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 On Sunday, April 18, 2010 10:32 AM Russell Dawson wrote: Thanks Ron. I will sleep easy tonight. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Ron Rosenfeld" wrote: On Sunday, April 18, 2010 5:18 PM Rick Rothstein wrote: Okay, here is yet another approach for you to try. If there are no spaces within the VAT number, then try this... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") If there could be spaces in the VAT number, and if they are in the positions shown when present, then use this formula instead... =IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""), ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid") -- Rick (MVP - Excel) On Sunday, April 18, 2010 5:24 PM Rick Rothstein wrote: Actually, stealing the comparison approach Bernard used (leave the negative value negative and make the last two digits negative instead), we get these even shorter formulas... If there are no spaces.... =IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid") If there could be spaces in the VAT number, and if they are in the positions shown when present... =IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""), ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid") -- Rick (MVP - Excel) |
Validation of UK VAT Number
Country code GB followed by either:
standard: 9 digits (block of 3, block of 4, block of 2 €“ e.g. GB999 9999 73) branch traders: 12 digits (as for 9 digits, followed by a block of 3 digits) government departments: the letters GD then 3 digits from 000 to 499 (e.g. GBGD001) health authorities: the letters HA then 3 digits from 500 to 999 (e.g. GBHA599) For the 9-digit scheme, the 2-digit block containing the 8th and 9th digits is always in the range 00 to 96 and is derived from a weighted modulus-97 check number (an identical algorithm is used for the 12-digit scheme, ignoring the extra 3-digit block).The current modulus-97 series is expected to run out during 2010, so a parallel series of numbers is being introduced from November 2009 for new registrations, restarting at 100 nnnn nn and following the same format but with the last two digits derived from an alternative algorithm known as €œ9755€³. The algorithm is identical to the one for the established series except that 55 is subtracted to give the check number (modulus 97), so the check number is either 55 less than or (if this would be negative) 42 greater than the check number that a VAT number in the established series would have if it were identical in the first seven digits. The details of the 97ˆ’55 check algorithm were to be secret but are now available from HMRC on request. The GD and HA formats may also be formatted as GB888 8xxx yy for EU compatibility, where xxx is the 3-digit number from the short format and yy is the 2-digit modulus-97 check number. Isle of Man registrations share the 9- and 12-digit formats with the UK, with GB as the country code prefix, but are distinguished by having 00 as the first two digits. Numbers with 01 to 09 in the first two digits are reserved by HM Revenue & Customs for UK non-VAT reference schemes. source: http://vat-number-validation.eu/ |
| All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com