View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JLatham JLatham is offline
external usenet poster
 
Posts: 2,203
Default 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.




.