Validation of identity document Spain

[d_alvarez]

Hello, in Spain 1 or 2 letters are used in the identity document number of a total of 9 digits that it consists of, these are calculated using an algorithm for calculating the check digit, the number is divided between 23 and the rest is replaced by a letter that is determined by inspection.

For example, if the DNI number is 12345678, divided by 23 days of remainder 14, then the letter would be Z: ------ 12345678Z.

http://www.interior.gob.es/web/servicios-al-ciudadano/dni/calculo-del-digito-de-control-del-nif-nie

I have managed to validate the documents that have 8 digits and a letter at the end, but I cannot validate the documents that start with a letter and end in a letter, having to divide the intermediate 7-digit number by 23 and get the remainder to apply the lyrics.

Could someone help me please ?

This is what I have been able to achieve

RIGHT([_THIS],1)=
INDEX(LIST(“T”,“R”,“W”,“A”,“G”,“M”,“Y”,“F”,“P”,“D”,“X”,“B”,“N”,“J”,“Z”,“S”,“Q”,“V”,“H”,“L”,“C”,“K”,“E”),1+MOD(NUMBER(LEFT([_THIS],8)),23))

[Austin]

Where are you using this number algorithm? is it a virtual column, real column, or just a valid if?
Another question are the number-letter conversions based on some math or just set conversions?

[d_alvarez]

Hello is a Valid If and a column of text

[Austin]

I will suggest maybe try the EXTRACTNUMBER() function.

help.appsheet.com

EXTRACTNUMBERS()

Extract list of numeric values within text value

Instead of NUMBER(LEFT([_THIS],8)) you could try EXTRACTNUMBER([_THIS])

[d_alvarez]

Excuse Austin_Lambeth could you make an example?

[d_alvarez]

To explain myself better, I have to validate a column and depending on its result, the data validation will be applied with the algorithm previously exposed.

I have 2 columns, one with a document type and the other where the document number will be entered.

Examples:

DNI: 12345678X

NIE: X1234567Z

[Austin]

Ok I read the link you had. So your expression works for DNI numbers and you now need an expression that works for NIE and DNI numbers?
So X1234567=01234567, Y1234567=11234567, and Z1234567=21234567? and then you need that right number MOD 23 to calculate your ending letter?

IFS(LEFT([_THIS],1)=“X”,
RIGHT([_THIS],1)=
INDEX(LIST(“T”,“R”,“W”,“A”,“G”,“M”,“Y”,“F”,“P”,“D”,“X”,“B”,“N”,“J”,“Z”,“S”,“Q”,“V”,“H”,“L”,“C”,“K”,“E”),1+MOD(NUMBER(LEFT(RIGHT([_THIS],8),7)),23)),
LEFT([_THIS],1)=“Y”,
RIGHT([_THIS],1)=
INDEX(LIST(“T”,“R”,“W”,“A”,“G”,“M”,“Y”,“F”,“P”,“D”,“X”,“B”,“N”,“J”,“Z”,“S”,“Q”,“V”,“H”,“L”,“C”,“K”,“E”),1+MOD(NUMBER(LEFT(“1”&RIGHT([_THIS],8),8)),23)),
LEFT([_THIS],1)=“Z”,
RIGHT([_THIS],1)=
INDEX(LIST(“T”,“R”,“W”,“A”,“G”,“M”,“Y”,“F”,“P”,“D”,“X”,“B”,“N”,“J”,“Z”,“S”,“Q”,“V”,“H”,“L”,“C”,“K”,“E”),1+MOD(NUMBER(LEFT(“2”&RIGHT([_THIS],8),8)),23)),
true,
RIGHT([_THIS],1)=
INDEX(LIST(“T”,“R”,“W”,“A”,“G”,“M”,“Y”,“F”,“P”,“D”,“X”,“B”,“N”,“J”,“Z”,“S”,“Q”,“V”,“H”,“L”,“C”,“K”,“E”),1+MOD(NUMBER(LEFT([_THIS],8)),23))
)

This is a very ugly formula. I am mildly confident in it.

[d_alvarez]

Sorry Austin_Lambeth I can’t find the missing parenthesis

[d_alvarez]

[Steve]

This is UNTESTED and based on my interpretation of the web page you referenced. I make no claim at all that this expression accurately validates an NIF/NIE. I STRONGLY encourage to do your own testing and prove it works as desired.

That said, try:

(
  RIGHT([DNI | NIE | CIF], 1)
  = MID(
    "TRWAGMYFPDXBNJZSQVHLCKE",
    (
      MOD(
        NUMBER(
          SWITCH(
            LEFT([DNI | NIE | CIF], 1),
            "X", "0",
            "Y", "1",
            "Z", "2",
            LEFT([DNI | NIE | CIF], 1)
          )
          & MID([DNI | NIE | CIF], 2, 7)
        ),
        23
      )
      + 1
    ),
    1
  )
)

1. SWITCH(LEFT([DNI | NIE | CIF], 1), ...) examines the first character of the ID string. If X, Y, or Z, that first character is replaced with 0, 1, or 2, respectively; otherwise, the first character is retained as-is.

2. ... & MID([DNI | NIE | CIF], 2, 7) appends the next seven characters of the original ID string to the first character computed by (1).

3. NUMBER(...) converts the string constructed by (2) to a Number (integer) value.

4. MOD(..., 23) finds the remainder from dividing the number from (3) by 23, producing a value between 0 and 22.

5. (MOD(...) + 1) increments the result of (4) by one to produce a value between 1 and 23, suitable for use as an index to the list of check-digits.

6. MID("...", (MOD(...) + 1), 1) finds the single check-digit corresponding to the value computed by (5).

7. (RIGHT(...) = MID(...) compares the check-digit in the ID string (RIGHT(...)) against the one computed by (6).

[d_alvarez]

Sorry Steve for checking back I have tried testing this function but the result is as follows.

It must be borne in mind that this validation should only act as a consequence of the result of the type of identification.
DNI
NIE
CIF
In the last CIF case, no validation will be performed, and the column is a TEXT column.

[Steve]

I made a typo in my initial expression here:

I’ve corrected it. Please use the revised expression above.

[Austin]

Try Steve’s formula first ^
Technically Steve’s formula will not work if the number is the 8 digit number with a letter at the end*

[Steve]

Austin_Lambeth:

Technically Steve’s formula will not work if the number is the 8 digit number with a letter at the end*

Huh?

[Austin]

I didn’t see this part of your switch statement whoops

[Austin]

Explanation of my formula,
If the left most character is X then take the right 8, then left 7, this will give you the 7 middle characters, mod 23 that and then run it verses your formula,
else if the left most character is Y then take the right 8 characters, concatenate a 1 to the front and then take the left 8 to get rid of the final letter, mod 23 run vs formula,
else if the left most character is z do the same as above but concatenate a 2 in front,
else run original formula.

[Austin]

There were quite a few missing parenthesis in my formula. This is what I get for making it in this forum post instead of the expression assistant. Mine is hopefully correct now too

[d_alvarez]

Hello, greatly grateful for your help,
the expressions are correct and functional the problem is that for it to work correctly, the results of the identification type column must be taken into account.

DNI
NIE
CIF

Since if the type of identification is a DNI this will only take 8 digits and 1 letter at the end and if the user enters a NIE this gives it as valid when it is not.

and when the identification type result is CIF, it should not be checked.

Is it possible that Valid If only works when the result of the identification type column is fulfilled?

This example should give an error.

[d_alvarez]

And in this example you shouldn’t validate

[Austin]

surround the whole expression in an if statement and have it be
IF([Tipo de Identificacion]=“CIF”,true,
EXPRESSION)
For mine you could just add [Tipo de Identificacion]=“CIF”,true, right after the IFS( and it would work but Steve’s would need the surrounding IF statement or you could use an OR([Tipo de Identificacion]=“CIF”,EXPRESSION)

[d_alvarez]

Sorry Austin could you please help me to include this function since I am not very clear about it, I would appreciate it a lot

[Austin]

Try:

IF([Tipo de Identificacion]="CIF",true,
(
  RIGHT([DNI | NIE | CIF], 1)
  = MID(
    "TRWAGMYFPDXBNJZSQVHLCKE",
    (
      MOD(
        NUMBER(
          SWITCH(
            LEFT([DNI | NIE | CIF], 1),
            "X", "0",
            "Y", "1",
            "Z", "2",
            LEFT([DNI | NIE | CIF], 1)
          )
          & MID([DNI | NIE | CIF], 2, 7)
        ),
        23
      )
      + 1
    ),
    1
  )
)
)

[Austin]

oh man that got ugly in the copy and paste

[Steve]

Fixed!