=$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
Bookmarks