I need a formula I can use in Excel 2013. My scanline data is in a single column in Excel and I need to apply the formula in Excel to generate the check digit.

Thank you in advance for your assistance

The details are as follows:

The scan line is 23 characters long – no spaces. It is as follows:

POSITIONS START – END LENGTH DESCRIPTION:

1-4 Last name alpha characters (4 characters)

5-12 Unit number (8 characters)

13-22 Amount owed (10 characters)

23 Check Digit

The check digit is being generated as “Modulus 10, sum of digits, with weights of 7,5,3,2.” with the letters replaced by numbers as in the image below.

NUMERIC VALUE | ALPHA VALUE | ALPHA VALUE | ALPHA VALUE |

1 | A | J | S |

2 | B | K | T |

3 | C | L | U |

4 | D | M | V |

5 | E | N | W |

6 | F | O | X |

7 | G | P | Y |

8 | H | Q | Z |

9 | I | R | |

Last Name | Unit Number | Amount Owed | |

Original Sequence | M U N O | 0 V 2 0 3 X 0 3 | 0 0 0 0 0 5 5 6 1 5 |

Converted to Numbers | 4 3 5 6 | 0 4 2 0 3 6 0 3 | 0 0 0 0 0 5 5 6 1 5 |

Multiply each number by: | 7 5 3 2 | 7 5 3 2 7 5 3 2 | 7 5 3 2 7 5 3 2 7 5 |

Result for each number | 28 15 15 12 | 0 20 6 0 21 30 0 6 | 0 0 0 0 0 25 15 12 7 25 |

Add digits | 10 6 6 3 | 0 2 6 0 3 3 0 6 | 0 0 0 0 0 7 6 3 7 7 |

Sum total | 75 | ||

Subtract last digit from 10 | 5 | ||

Final OCR Line | M U N O | 0 V 2 0 3 X 0 3 | 0 0 0 0 0 5 5 6 1 5 5 |