Here's what I worked out based on my post yesterday.
=VALUE(IF(ISNA(VLOOKUP(MID(D2,1,1),$L$2:$M$11,2)), "",VLOOKUP(MID(D2,1,1),$L$2:$M$11,2))&IF(ISNA(VLOO KUP(MID(D2,2,1),$L$2:$M$11,2)),"",VLOOKUP(MID(D2,2 ,1),$L$2:$M$11,2))&IF(ISNA(VLOOKUP(MID(D2,3,1),$L$ 2:$M$11,2)),"",VLOOKUP(MID(D2,3,1),$L$2:$M$11,2))& IF(ISNA(VLOOKUP(MID(D2,4,1),$L$2:$M$11,2)),"",VLOO KUP(MID(D2,4,1),$L$2:$M$11,2))&IF(ISNA(VLOOKUP(MID (D2,5,1),$L$2:$M$11,2)),"",VLOOKUP(MID(D2,5,1),$L$ 2:$M$11,2))&IF(ISNA(VLOOKUP(MID(D2,6,1),$L$2:$M$11 ,2)),"",VLOOKUP(MID(D2,6,1),$L$2:$M$11,2)))/100
I put it in E2 with the letters in D2. Also, the range is L2:M11 t
give the nubers 1,2,3...0 rather than L2:M12 as in you post.
Give it a whirl...
--
Message posted from
http://www.ExcelForum.com