A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Validation of UK VAT Number



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 09:51 PM posted to microsoft.public.excel.worksheet.functions
Rob
external usenet poster
 
Posts: 63
Default Validation of UK VAT Number

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.


  #2  
Old April 16th, 2010, 10:19 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Validation of UK VAT Number

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.




  #3  
Old April 17th, 2010, 12:00 AM posted to microsoft.public.excel.worksheet.functions
Ron@Buy
external usenet poster
 
Posts: 348
Default Validation of UK VAT Number

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:
=D130 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(D511)
Cell D13 enter
=IF(B13="",0,IF(D12-970,D12-97,IF(D12-97-970,D12-97-97,D12-97-97-97)))
Either hide column D or format D513 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.


.

  #4  
Old April 17th, 2010, 02:00 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Validation of UK VAT Number

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.


.

  #5  
Old April 17th, 2010, 02:07 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Validation of UK VAT Number

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.


.

  #6  
Old April 17th, 2010, 02:11 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Validation of UK VAT Number

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.




.

  #7  
Old April 17th, 2010, 02:20 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
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.




.

  #8  
Old April 17th, 2010, 02:56 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Validation of UK VAT Number

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 digitsspace4digitsspace2digits?

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.




.



  #9  
Old April 17th, 2010, 03:01 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Validation of UK VAT Number

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.


.

  #10  
Old April 17th, 2010, 05:55 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default Validation of UK VAT Number

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 digitsspace4digitsspace2digits?

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.




.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.