There’s a tool for doing this validation on the EU commission site, but as it only takes one VAT number at a time, it takes quite a lot of effort to go through a long list of clients. To make this a bit easier, I created a custom function for doing the validation in a Google Docs spreadsheet; you can test it here (make a copy for your own use) To use the function in your own spreadsheets, in the menus go to Tools: Scripts: Script Editor and copy everything, then create a new script in your own spreadsheet and paste it there. It would be very easy to make a version of this for Excel too – if you are interested, please contact me by email.
The usage of the function is very simple, just input the VAT number and two letter country code as parameters. If the country code parameter is omitted, the function will assume the first two characters of the VAT number contain the country code. The function will then indicate whether the number is valid or not. For some countries, it will also show the name of the company.
The function screenscrapes the results from the EU commission tool – if that tool is changed then the function may break. Note that I don’t make any guarantees for the accuracy of the results.