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
  #21  
Old April 18th, 2010, 03:32 PM posted to microsoft.public.excel.worksheet.functions
Russell Dawson[_2_]
external usenet poster
 
Posts: 173
Default Validation of UK VAT Number

Thanks Ron.

I'll sleep easy tonight.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Ron Rosenfeld" wrote:

On Sat, 17 Apr 2010 23:39:01 -0700, Russell Dawson
wrote:

That's fine. It tells you the format i.e. 9 or 12 digits and the 3-4-2
format is no secret. It couldn't be as all businesses have to display it in
invoices etc.

What it doesn't give is the series of calculations apparently used to create
what might appear to be a VAT number to anybody duped into accepting that
number, first by it's issue and then confirmed as being valid via the sites
you mentioned.

I suspect that the validation site would only be using the appropriate
calculation to validate the number as a possible VAT number because it passes
the arithmetical test rather than check if the number has been issued and
therefore a true VAT registration number.

I'd be interested to know why Rob needs to do this.


The Modulus 97 algorithm is not secret.

See http://sima.cat/nif.php

But I don't know if the new modulus 9755 algorithm, which is supposed to be
being implemented in GB this year to increase the range of allowable VAT
numbers, is secret or not. Certainly it can be obtained with a legitimate
business reason.
--ron
.

  #22  
Old April 18th, 2010, 10:18 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Validation of UK VAT Number

Okay, here is yet another approach for you to try.

If there are no spaces within the VAT number, then try this...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

If there could be spaces in the VAT number, and if they are in the positions
shown when present, then use this formula instead...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""),
ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

--
Rick (MVP - Excel)



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


  #23  
Old April 18th, 2010, 10:24 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Validation of UK VAT Number

Actually, stealing the comparison approach Bernard used (leave the negative
value negative and make the last two digits negative instead), we get these
even shorter formulas...

If there are no spaces....

=IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid")

If there could be spaces in the VAT number, and if they are in the positions
shown when present...

=IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""),
ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid")

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Okay, here is yet another approach for you to try.

If there are no spaces within the VAT number, then try this...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

If there could be spaces in the VAT number, and if they are in the
positions shown when present, then use this formula instead...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""),
ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

--
Rick (MVP - Excel)



"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 03:55 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.