Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compound Formula for Substitution
I need to try to create a formula to convert numbers to letters using the
following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1) ,A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J 2,2)) M2 is my starting number and the table array is the Number/Letter Conversion Chart. I truly hope someone can help me. Thank you, Chris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compound Formula for Substitution
If you can download and install the free add-in Morefunct.xll from:
http://xcell05.free.fr/morefunc/english/index.htm Alternate download site: http://www.download.com/Morefunc/300...-10423159.html Then you can use an array formula** like this: =MCONCAT(LOOKUP(--MID(SUBSTITUTE(C1,".",""),ROW(INDIRECT("1:"&LEN(C1 )-COUNT(FIND(".",C1)))),1),A1:B10)) Whe C1 = some number A1:B10 = lookup table ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Chris" wrote in message ... I need to try to create a formula to convert numbers to letters using the following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1) ,A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J 2,2)) M2 is my starting number and the table array is the Number/Letter Conversion Chart. I truly hope someone can help me. Thank you, Chris |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compound Formula for Substitution
P.S.
If your number is *always* only 4 digits then this can be done with a much simpler formula. You should have posted several examples of the numbers you're dealing with. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If you can download and install the free add-in Morefunct.xll from: http://xcell05.free.fr/morefunc/english/index.htm Alternate download site: http://www.download.com/Morefunc/300...-10423159.html Then you can use an array formula** like this: =MCONCAT(LOOKUP(--MID(SUBSTITUTE(C1,".",""),ROW(INDIRECT("1:"&LEN(C1 )-COUNT(FIND(".",C1)))),1),A1:B10)) Whe C1 = some number A1:B10 = lookup table ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Chris" wrote in message ... I need to try to create a formula to convert numbers to letters using the following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1) ,A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J 2,2)) M2 is my starting number and the table array is the Number/Letter Conversion Chart. I truly hope someone can help me. Thank you, Chris |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compound Formula for Substitution
How about a UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. In cell A1 = 12.34 In cell B1 try the below formula =convertdata(A1) Function ConvertData(varData As Variant) As String Dim intCount As Integer For intCount = 1 To Len(varData.Value) If IsNumeric(Mid(varData.Value, intCount, 1)) Then ConvertData = ConvertData & Chr(65 + Mid(varData.Value, intCount, 1)) End If Next End Function -- Jacob "Chris" wrote: I need to try to create a formula to convert numbers to letters using the following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1) ,A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J 2,2)) M2 is my starting number and the table array is the Number/Letter Conversion Chart. I truly hope someone can help me. Thank you, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with parameter substitution in formula (&value), vbScript | Excel Worksheet Functions | |||
variable substitution in a formula linking to external workbook | Excel Discussion (Misc queries) | |||
variable substitution in a formula | Excel Discussion (Misc queries) | |||
Cell Formula substitution. | Excel Discussion (Misc queries) | |||
why does ON = Ambiguous in substitution formula? | Excel Worksheet Functions |