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
|