European VAT tester

[Felix Atagong]

I'm looking for the several algorythms to test if a VAT code within the European community is valid or not.

For instance: Belgium has a 9 digit VAT code and
MOD((LEFT(VAT,7)+RIGHT(VAT,2))/97) should be 0.

Does someone else has the formulae (or is it formulas ) for other countries? For one reason or another the website of the European community won't give it? But if we all pull together as a team, I'll maybe get there...

 

[Legacy 1363]

For one reason or another the website of the European community won't give it?

Well ... yes. This is normal. But what is the "one reason or another" in this case?

 

[erik.van.geit]

Felix,

Just to remind you and all our Belgian Friends that our Vat-code is extended to 10 digits since January 1 2005.

784166212 became 0784166212

kind regards,
Erik

 

[Felix Atagong]

Just to remind you and all our Belgian Friends that our Vat-code is extended to 10 digits since January 1 2005.

I forgot that completely. It was intended to simplify things wasn't it?

 

[erik.van.geit]

It was intended to simplify things wasn't it?

yes, it allowed all Excel-gurus in Belgium to make some money: changing formats in clients sheets and some macros

kind regards,
Erik

 

[Chris Davison]

Felix,

ours is MOD 97 too....

I have the formula at work, I'll pull it out on Friday - I think I did a thread on this YEARS ago too when I got our INtrastat nomenclature forms dumped on my desk

I was rather hoping we'd get a formula for all 12 EU countries then, but what is it now ? 20 countries or something ?

Chris
(UK)

edit for old thread http://www.mrexcel.com/board2/viewtopic.php?t=193&highlight=vat

 

[Felix Atagong]

I grabbed this from the EU website:
http://europa.eu.int/comm/taxation_customs/vies/en/faqvies.htm#item11

Q11: Is it possible to know the algorithms used by Member States in the construction of their VAT identification numbers?

The European Commission cannot divulge these algorithms. However, the structure of VAT identification numbers is given in the table below.

VAT identification number structure
--------------------------------------------------------------------------------
Member State Structure Format*
--------------------------------------------------------------------------------
AT-Austria ATU999999991 1 block of 9 characters
--------------------------------------------------------------------------------
BE-Belgium BE999999999 or
BE09999999992 1 block of 9 digits or
1 block of 10 digits 3
--------------------------------------------------------------------------------
CY-Cyprus CY99999999L 1 block of 9 characters
--------------------------------------------------------------------------------
CZ-Czech Republic CZ99999999 or
CZ999999999 or
CZ9999999999
1 block of either 8, 9 or 10 digits
--------------------------------------------------------------------------------
DE-Germany DE999999999 1 block of 9 digits
--------------------------------------------------------------------------------
DK-Denmark DK99 99 99 99 4 blocks of 2 digits
--------------------------------------------------------------------------------
EE-Estonia EE999999999 1 block of 9 digits
--------------------------------------------------------------------------------
EL-Greece EL999999999 1 block of 9 digits
--------------------------------------------------------------------------------
ES-Spain ESX9999999X4 1 block of 9 characters
--------------------------------------------------------------------------------
FI-Finland FI99999999 1 block of 8 digits
--------------------------------------------------------------------------------
FR-France FRXX 999999999 1 block of 2 characters, 1 block of 9 digits
--------------------------------------------------------------------------------
GB-United Kingdom GB999 9999 99 or
GB999 9999 99 9995 or
GBGD9996 or
GBHA9997 1 block of 3 digits, 1 block of 4 digits and 1 block of 2 digits; or the above followed by a block of 3 digits; or 1 block of 5 characters
--------------------------------------------------------------------------------
HU-Hungary HU99999999 1 block of 8 digits
--------------------------------------------------------------------------------
IE-Ireland IE9S99999L 1 block of 8 characters
--------------------------------------------------------------------------------
IT-Italy IT99999999999 1 block of 11 digits
--------------------------------------------------------------------------------
LT-Lithuania LT999999999 or
LT999999999999 1 block of 9 digits, or 1 block of 12 digits
--------------------------------------------------------------------------------
LU-Luxembourg LU99999999 1 block of 8 digits
--------------------------------------------------------------------------------
LV-Latvia LV99999999999 1 block of 11 digits
--------------------------------------------------------------------------------
MT-Malta MT99999999 1 block of 8 digits
--------------------------------------------------------------------------------
NL-The Netherlands NL999999999B998 1 block of 12 characters
--------------------------------------------------------------------------------
PL-Poland PL9999999999 1 block of 10 digits
--------------------------------------------------------------------------------
PT-Portugal PT999999999 1 block of 9 digits
--------------------------------------------------------------------------------
SE-Sweden SE999999999999 1 block of 12 digits
--------------------------------------------------------------------------------
SI-Slovenia SI99999999 1 block of 8 digits
--------------------------------------------------------------------------------
SK-Slovakia SK9999999999 1 block of 10 digits
--------------------------------------------------------------------------------

Remarks:
*: Format excludes 2 letter alpha prefix
9: A digit
X: A letter or a digit
S: A letter; a digit; "+" or "*"
L: A letter

Notes:
1: The 1st position following the prefix is always "U".
2: The first digit following the prefix is always zero ('0').
3: The VAT number of a Belgian trader can appear in any of these two formats. The (new) 10-digit format is the result of adding a leading zero to the (old) 9-digit format.
4: The first and last characters may be alpha or numeric; but they may not both be numeric.
5: Identifies branch traders.
6: Identifies Government Departments.
7: Identifies Health Authorities.
8: The 10th position following the prefix is always "B".

 

[Chris Davison]

Felix,

this worked for our normal VAT numbers ignoring any spaces :

=IF(MOD(SUM((MID(A1,{1;2;3;4;5;6;7;8;9},1)*({8;7;6;5;4;3;2;10;1}))),97)=0,"Okay","Error")



this concludes the voting from the UK jury....



edit - here's a couple of UK VAT numbers that you can test this on :

674 9453 88 and 240 2000 43

 

[Felix Atagong]

Thanks, I'll include that on my sheet as well. Two tests is better than none.
Come on European gals and guys, who dares to answer!