Ask a new question

Creating Checksum Digit in Excel

I have a column of about 2000 7-digit ID numbers in microsoft excel.  I would like to take those ID number and create another column that has the ID number plus the checksum digit.  I know there is some fancy formula but I have no idea how to do any of it.  Any help would be great.
Answer
Answer
alicia wrote:
I have a column of about 2000 7-digit ID numbers in microsoft excel.  I would like to take those ID number and create another column that has the ID number plus the checksum digit.  I know there is some fancy formula but I have no idea how to do any of it.

There are all kinds of methods for creating checksums, some better than others for various technical reasons.  The following is a simple method that sums the digits:

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)))

Caveat:  The use of INDIRECT makes this a volatile formula, which is recalculated every time any cell in any worksheet in the workbook is edited.  You can mitigate that by writing ROW(A1:INDEX(A1:A7,LEN(A1))) instead.  But that expression will change if you insert rows between the current row and 6 rows below.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

I'm sorry that I'm replying twice (I know you asked me not to) but I think I figured it out!!!

 

I am so, so, so thankful to you for your hard work.  You have helped us so much and I wish I could buy you a bottle of wine or something!!

 

Thank you again for your patience with me!!

 

Alicia Minor

 

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 25, 2024 Views 23,567 Applies to: