Validation of identity document Spain

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

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?

Hello is a Valid If and a column of text

I will suggest maybe try the EXTRACTNUMBER() function.


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

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

Excuse Austin_Lambeth could you make an example?

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.

2 Likes

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

2 Likes

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*

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.

1 Like

Huh?

:confused:

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.

1 Like

Sorry Austin_Lambeth I can’t find the missing parenthesis

I made a typo in my initial expression here:

image

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

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

1 Like

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 :slight_smile:

1 Like

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?

image

This example should give an error.

And in this example you shouldn’t validate :sob: :sob:

image

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)

1 Like