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