How to calculate the DNI letter in Excel

  • The DNI in Spain consists of eight numbers plus a control letter.
  • Calculating the DNI letter can be easily done with Excel using specific functions.
  • There are alternative methods for calculating the letter, such as manual formulas and web pages.
  • Using online tools guarantees fast and accurate results for obtaining the letter of the DNI.

letter dni excel

The number of the National Identity o DNI will accompany us throughout our lives and we will have to use it on numerous occasions and for all kinds of procedures. In Spain, this document consists of a series of eight numbers with a letter at the end (the check digit). In this post we will see how calculate the letter of the DNI in Excel or resorting to other methods.

Most people know their ID number by heart, letter included. However, it often happens that we get confused when it comes to checking the numbers of various documents. This happens very frequently to fathers and mothers with the DNI of the whole family, to the teachers who take their class on excursions or on a study trip, to the coaches of children's teams when registering for tournaments, etc There are many cases where it can be very useful to know how to calculate that letter.

The real DNI letter utility is to prevent anyone from using an invented number when carrying out any procedure. If we use a certain number and put the wrong letter at the end, the error message often jumps.

But the truth is that the letter of the DNI (also the NIF or NIE) can be calculated from the numbers in the document itself. This was legally established in article 11 of Royal Decree 1553/2005, of December 23: «The National Identity Document will collect the personal number of the DNI and the verification character corresponding to the Tax Identification number». 

This means that it is possible to calculate the letter of the DNI with a series of tricks and simple processes. Also through Excel.

Calculate the letter of the DNI with Excel

calculate dni letter in excel

The Excel spreadsheet will give us the data we are looking for (the DNI letter) by applying two simple formulas: the EXTRACT text function and mathematical function RESIDUE. These are the steps to follow to achieve our goal:

  1. First of all, we open Excel and, in cell A1, We enter the ID number of which we want to know the lyrics.
  2. Then, in cell B1 we insert the RESIDUE function with the following syntax: =RESIDUAL(A1).
  3. Next, we use the MID function to perform the calculation automatically with this syntax:
     =EXTRACT("TRWAGMYFPDXBNJZSQVHLCK";RESIDUAL(A1;23)+1;1).

By doing this, the DNI letter will appear in cell B1, just after the document number in cell A1. That easy.

The main advantage of calculating the DNI letter in Excel is that we obtain totally reliable resultsavoiding human error. It is also very convenient if we need to calculate large volumes of data, since the entire process can be automated.

Other methods to calculate the letter of the DNI

Calculating the letter of the DNI using Excel is a practical and infallible method, but not the only one. In fact, these systems began to become popular since 1990 when a DNI with a letter was issued for the first time in Spain.

Taking this measure was necessary, in view of the numerous errors recorded: incomplete numbers, duplicates, with the numbering changed, etc. According to the Ministry of Finance, at the end of the 80s about 30% of Spanish identity documents were incorrect. A situation that made administrative procedures of all kinds really complicated.

These are some of the methods that have been used the most since then and are still equally valid today:

Formula to know the letter of the DNI

There is a simple formula that allows us to calculate the control letter or digit corresponding to a DNI number. We will only need a calculator, or not even that if we are agile in mental calculation. The method consists of Divide the full ID number by 23 (rounding the result).

El rest, which will always be between the numbers 0 and 22, will allow us to find the letter we are looking for. This is the table (provided by the website of the Ministry of the Interior) in which we can consult the lyrics:

  • 0=T
  • 1=R
  • 2=W
  • 3 = A
  • 4=G
  • 5=M
  • 6 = AND
  • 7=F
  • 8 = P
  • 9 = EASY
  • 10 = X
  • 11 = OVER
  • 12 = N
  • 13 = J
  • 14 = Z
  • 15 = Yes
  • 16 = Q
  • 17 = V
  • 18=H
  • 19=L
  • 20=C
  • 21=K
  • 22 = E

Websites to calculate the letter of the DNI

cartanif.com

To know the result in a much faster way, it is best to use a specific website to find out the letter of the DNI. These are some recommended ones:

letranif.com

Drive cartanif.com It doesn't have any complications. All you have to do is enter the numbers of the national identity document and press the button. The page will calculate it automatically in less than a second. The best thing is that the results are completely accurate and reliable.

Calculardni.es

The web calculatedni.es It works exactly the same as the previous one, although it offers an added advantage: it also allows us to calculate the letter of the NIE.

Calculate -letra-dni.appspot.com

Yet another website to make this query: Calculate -letra-dni.appspot.com, with the same operation as the others and with exact and immediate results.


Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.