Validation of UK VAT Number

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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.
 
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(B13>0,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.
 
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 somewhere:
=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")
 
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"))
 
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??
 
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<space>4digits<space>2digits?

It would be better if the OP could post *several* examples of both valid and
invalid numbers so we can test more thoroughly.
 
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
 
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.
 
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
 
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.
Click to expand...

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
 
Thanks Ron, another brilliant way to achieve the result.

Ta, Rob
Click to expand...

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
 
Thanks Ron, another brilliant way to achieve the result.

Ta, Rob
Click to expand...

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
 
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.
Click to expand...

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
 
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
 
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")
 
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")
 
Back
Top