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)?
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:
