calculate value based by replacing letters with numbers

Hello!

I need to take a long alphanumeric number and assign the letter's predetermined values and based on those values mulitply and divide them out for a final figure. Or would it be easier to within an Excel cell with a formula?

for example -

OOLU 708695.


Each letter of the prefix has its own value:

A 10 J 20 S 30
B 12 K 21 T 31
C 13 L 23 U 32
D 14 M 24 V 34
E 15 N 25 W 35
F 16 O 26 X 36
G 17 P 27 Y 37
H 18 Q 28 Z38
I 19 R 29



Each figure of the number has its own value.

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
0 0



We know that: Now calculate:


O=26 26 X 1 = 26
O=6 26 X 2 = 52
L=23 23 X 4 = 92
U=32 32 X 8 = 256
7=7 X 16 = 112
0=0 0 X 32 = 0
8=8 8 X 64 = 512
6=6 6 X 128 = 768
9=9 9 X 256 = 2304
5=5 5 X 512 = 2560

--------------------------------------------------------------------------------

Total = 6682


Now divide this total by 11:
6682 / 11 = 607.4545


and take the figures after the decimal
and multiply it by 11 to get the check digit.
0.4545 X 11 = 5


The complete number is:
OOLU 708695-5

what do you think?

thanks!

Based on the sample... if you create a table which has:

Column 1:
holding the values 0-Z (formatted as text pre entry such that 0-9 are text)

Column 2:
associated numeric value

Then:

=$A$1&"-"&ROUND(MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(A$1:INDEX(A:A,LEN(A$1)-1)),1),E1:F36),2^(ROW(A$1:INDEX(A:A,LEN(A$1)-1))-1))/11,1)*11,0)

should generate the OOLU 708695-5 result.

edit: should add E1:F36 in the above is of course my lookup table of 0-Z and associated numeric value

Hello jbowling,

Here is VBA macro that you can use, as long as the number format remains the same: 4 digits, a space, and 5 digits. This macro is a UDF, so it can be used on a worksheet like a formula.

Function AddCheckDigit(ByVal NumStr As String)

  Dim Alphas As Variant
  Dim CheckDigit As Integer
  Dim Digit As String
  Dim N As Double
  Dim S As String
  
    S = Replace(NumStr, " ", "")
    Alphas = Array(10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, _
                   25, 26, 27, 28, 29, 30, 31, 32, 34, 35, 36, 37, 38)
                   
    For I = 0 To 9
      Digit = Mid(S, I + 1, 1)
      A = Asc(Digit)
      Select Case A
        Case 65 To 90
          N = N + (Alphas(A - 65) * (2 ^ I))
        Case 48 To 57
          N = N + ((A - 48) * (2 ^ I))
      End Select
    Next I
    
   CheckDigit = ((N / 11) - Int(N / 11)) * 11
   AddCheckDigit = NumStr & "-" & CheckDigit
    
End Function

Originally Posted by jbowling

Or even better, can i combine ... [formula] ... with
=RIGHT(C5,4)*11/10000

which would give me the number i'm looking for in one cell and i could just hand out the formula instead.

Perhaps I'm mistaken but I thought the formula in post # 2 generated the complete string with check digit in place ...

(if you prefer the use of inline array constants to a basic 2 column table range then substitute appropriately)

My point was, the formula in post # 2 does what you need - simply substitute references to E1:F36 with the inline array from Bob's formula (the inline array is simply a hard wired alternative to a range)

Or, if preferred add the parts from post # 2 that are not in Bob's formula.

In reality the two formulas are pretty much identical in all other respects.

Post # 2 using inline - below.

=$A$1&"-"&ROUND(MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(A$1:INDEX(A:A,LEN(A$1)-1)),1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L" ,"M","N","O","P","Q","R","S","T","U","Vv","W","X","Y","Z"},{0,1,2,3,4,5,6,7,8,9,10,12,13,14,15,16,17 ,18,19,20,21,23,24,25,26,27,28,29,30,31,32,34,35,36,37,38}),2^(ROW(A$1:INDEX(A:A,LEN(A$1)-1))-1))/11,1)*11,0)

edit: where A1 holds the original alpha numeric code (modify reference as appropriate)

... but it is industry wide.

Out of curiosity, what industry?

So the question would then be: is there a space in the alpha numeric or not ?

Your latest post implies not whereas your first post implies the opposite to be true.

If there is not (or it varies) then use:

=$A$1&"-"&ROUND(MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A1," ",""),ROW(A$1:INDEX(A:A,LEN(SUBSTITUTE(A1," ","")))),1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","Vv","W","X","Y","Z"},{0,1,2,3,4,5,6,7,8,9,10,12,13,14,15,16,17,18,19,20,21,23,24,25,26,27,28,29,30,31,32,34,35,36,37,38}),2^(ROW(A$1:INDEX(A:A,LEN(SUBSTITUTE(A1," ",""))))-1))/11,1)*11,0)

Edit: the above would only work with XL2007 and beyond I'm afraid given levels of nesting... for versions prior to that it would be best to split the function parts
(apologies I should have checked on that basis)

to be honest i could go either way, if i just give the final digit it can be used as needed but if it is added to the end that info could be recognized as well.

I'm not sure to whom the last post was aimed - I was not talking about the checkdigit.

Your first post states:

OOLU 708695

Your latter post states:

OOLU708695

the space has an impact in so far as it generally warrants use of a Substitute which in turn takes you outside permitted levels of nesting (using the INDEX based approach).

If you want a single cell native function it has a direct impact on the suggested approaches.

A UDF makes a great deal of sense IMO - the SUMPRODUCT is inefficient not to mention unwieldy.

Too many emails and posts at the same time.

The space in the middle is a non factor, it can be there or not it will not affect anything I need it for.

and we are working Excel 2003 so it looks like i might have to keep this in 2 pieces as far the formula goes.

Originally Posted by jbowling

The space in the middle is a non factor, it can be there or not it will not affect anything I need it for.

and we are working Excel 2003 so it looks like i might have to keep this in 2 pieces as far the formula goes.

If the spaces are never to exist then you can dispense with the SUBSTITUTEs which should in turn remove nesting issues and thereby permit use pre XL2007:

=A1&"-"&ROUND(MOD(SUMPRODUCT(LOOKUP(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},{0,1,2,3,4,5,6,7,8,9,10,12,13,14,15,16,17,18,19,20,21,23,24,25,26,27,28,29,30,31,32,34,35,36,37,38}),2^(ROW(A$1:INDEX(A:A,LEN(A1)))-1))/11,1)*11,0)

but I would still opt for UDF...

=$a$1&"-"&round(mod(sumproduct(search(mid(a1;row(a$1:index(a:a;len(a1)));1);"0123456789a bcdefghijk lmnopqrstu vwxyz")-1;2^(row(a$1:index(a:a;len(a1)))-1))/11;1)*11;0)

Taking off from snb's formula,

=A1 & "-" & MOD(ROUND(MOD(SUMPRODUCT(FIND(MID(A1, {1,2,3,4,5,6,7,8,9,10}, 1), "0123456789A BCDEFGHIJK LMNOPQRSTU VWXYZ") - 1, 2^{0,1,2,3,4,5,6,7,8,9})/11, 1) * 11, 0), 10)

EDIT: or

=A1 & "-" & MOD(MOD(SUMPRODUCT(FIND(MID(A1, {1,2,3,4,5,6,7,8,9,10}, 1), "0123456789A BCDEFGHIJK LMNOPQRSTU VWXYZ") - 1, 2^{0,1,2,3,4,5,6,7,8,9}), 11), 10)


No embedded spaces