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.

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.

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.


.

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.


.

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.


.

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.




.

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.




.

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.




.

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.


.

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.




.



.

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.

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.




.

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

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

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.




.

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

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.




.

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

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

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.

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
.

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 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.

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)

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/