SOLVED

Excel -- Converting letters to number

%3CLINGO-SUB%20id%3D%22lingo-sub-236507%22%20slang%3D%22en-US%22%3EExcel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236507%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%26nbsp%3B%20Hoping%20I%20could%20find%20some%20help%3A%26nbsp%3B%20I%20need%20to%20convert%20a%20string%20of%20letters%20into%20numbers%2C%20and%20vice%20versa.%26nbsp%3B%20The%20set-up%3A%20the%20numbers%26nbsp%3B1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%2C0%26nbsp%3Bcorrespond%20to%20letters%20a%2Cb%2Cc%2Cd%2Ce%2Cf%2Cg%2Ch%2Ci%2Cj.%26nbsp%3B%20Example%3A%20If%20a%20user%20enters%20%3CEM%3Ehajg%3C%2FEM%3E%20in%26nbsp%3Bthe%20cell%2C%20the%20formula%20(or%20macro)%20produces%20%3CEM%3E8107%3C%2FEM%3E.%26nbsp%3B%20Or%20%3CEM%3Edibe%3C%2FEM%3E%20produces%20%3CEM%3E4925%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20have%20two%20formulas%2C%20so%20the%20conversion%20doesn't%20have%20to%20go%20both%20ways%20in%20one%20formula.%26nbsp%3B%20I%20just%20need%20to%20do%20both.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20out%20there%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-236507%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321376%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321376%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Jason%2C%3C%2FP%3E%3CP%3EI%20suggest%20you%20use%26nbsp%3B%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fzetexcel.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fzetexcel.com%2F%3C%2FA%3E%3C%2FEM%3E.%20It%20has%26nbsp%3B%3CSPAN%3Ethe%20ability%20to%20generate%2C%20modify%2C%20convert%2C%20render%20and%20print%20spreadsheets%20without%20using%20Microsoft%20Excel%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320237%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320237%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%20%3DCODE(UPPER(A1))-64%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20will%20work%20for%20uppercase%20and%20lowercase.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236770%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236770%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20wondering%20about%20the%20numbers%20to%20letters%20--%20I%20figured%20it%20was%20just%20like%20your%20suggestion%2C%20but%20I%20wasn't%20sure.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236768%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236768%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20works%20wonderfully!%26nbsp%3B%20Thank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236762%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236762%22%20slang%3D%22en-US%22%3E%3CP%3EHaytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20This%20is%20precisely%20what%20I%20was%20looking%20for.%26nbsp%3B%20I%20appreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EJason%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236597%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236597%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jason%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20familiar%20with%20VBA%20in%20Excel%20you%20can%20create%20a%20custom%20function%20to%20do%20this%20and%20use%20it%20in%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20suggested%20function%3A%3C%2FP%3E%3CPRE%3EOption%20Compare%20Text%3CBR%20%2F%3E%3CBR%20%2F%3EFunction%20LettersToNumbers(text%20As%20String)%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20i%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20result%20As%20String%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20i%20%3D%201%20To%20Len(text)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Select%20Case%20Mid(text%2C%20i%2C%201)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22a%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22b%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%202%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22c%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%203%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22d%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%204%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22e%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%205%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22f%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%206%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22g%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%207%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22h%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%208%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22i%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%209%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Case%20%22j%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20result%20%3D%20result%20%26amp%3B%200%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20LettersToNumbers%20%3D%20result%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20follow%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fvba%2Fexamples%2Fuser-defined-function.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E%20to%20figure%20out%20how%20to%20insert%20this%20function%20into%20your%20own%20workbook.%3C%2FP%3E%3CP%3EAlso%2C%20you%20can%20place%20it%20in%20the%20Personal%20Macro%20Workbook%20so%20that%20the%20function%20will%20be%20available%20in%20all%20open%20workbook%20and%20this%20is%20described%20here%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.myonlinetraininghub.com%2Fmoving-vba-code-and-forms-into-personal-xlsb%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3EHowever%2C%20I've%20inserted%20it%20into%20the%20attached%20workbook.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236577%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236577%22%20slang%3D%22en-US%22%3E%3CP%3EMr.%20Lewin%3C%2FP%3E%3CP%3Enice%20formula!%3C%2FP%3E%3CP%3Ethen%20numbers%20to%20letters%3A%20%3DSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2C1%2C%22a%22)%2C2%2C%22b%22)%2C3%2C%22c%22)%2C4%2C%22d%22)%2C5%2C%22e%22)%2C6%2C%22f%22)%2C7%2C%22g%22)%2C8%2C%22h%22)%2C9%2C%22i%22)%2C0%2C%22j%22)%3C%2FP%3E%3CP%3Eto%20extend%20-%20add%20equivalent%20numbers%20of%20%22SUBSTITUTE(%22%20to%20the%20formula%3C%2FP%3E%3CP%3Ei.e.%20there%20are%2010%20substitutions%20-%20therefore%2010%20%22SUBSTITUTE(%22%3C%2FP%3E%3CP%3Ehope%20I%20am%20right...%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236549%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236549%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Jason%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20a%20few%20nested%26nbsp%3B%3CSPAN%3ESUBSTITUTE().%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3E%3DSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1%2C%22a%22%2C1)%2C%22b%22%2C2)%2C%22c%22%2C3)%2C%22d%22%2C4)%2C%22e%22%2C5)%2C%22f%22%2C6)%2C%22g%22%2C7)%2C%22h%22%2C8)%2C%22i%22%2C9)%2C%22j%22%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798074%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bthis%20is%20brilliant!%26nbsp%3B%20%26nbsp%3BHowever%2C%20I%20couldn't%20figure%20out%20how%20to%20do%20numbers%20to%20letters%2C%20it%20show%20show%20%23VALUE!...%3C%2FP%3E%3CP%3EDo%20you%20think%20you%20could%20shine%20some%20light%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%20so%20much!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798158%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F180354%22%20target%3D%22_blank%22%3E%40Jason%20Summer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20formulas%20could%20be%3C%2FP%3E%0A%3CP%3Etext%3D%26gt%3Bnumber%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3D--TEXTJOIN(%22%22%2CTRUE%2CMOD(CODE(LOWER(MID(A1%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%248%2CLEN(A1)))%2C1)))-96%2C10))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Enumber%3D%26gt%3Btext%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DTEXTJOIN(%22%22%2CTRUE%2CCHAR(%20MOD(MID(A2%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%248%2CLEN(A2)))%2C1)-1%2C10)%2B1%2B96))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126443i018B91E0C01051EB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECSE%20(array)%20formula%20for%20pre-DA%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798281%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390070%22%20target%3D%22_blank%22%3E%40jojog%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20that%2C%20you%20need%20to%20change%20the%20above%20function%20as%20follows%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EFunction%20LettersToNumbers(text%20As%20String)%20As%20String%0A%20%20%20%20%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20Dim%20result%20As%20String%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20For%20i%20%3D%201%20To%20Len(text)%0A%20%20%20%20%20%20%20%20%20%20%20%20Select%20Case%20Mid(text%2C%20i%2C%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22a%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%202%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22b%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%203%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22c%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%204%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22d%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%205%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22e%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%206%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22f%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%207%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22g%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%208%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22h%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%209%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22i%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20Case%200%0A%20%20%20%20%20%20%20%20%20%20%20%20%20result%20%3D%20result%20%26amp%3B%20%22j%22%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20%20%20%20%20Next%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20LettersToNumbers%20%3D%20result%0A%20%20%20%20%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20that%20you%20have%20changed%20it%20like%20this%20but%20you%20forgot%20to%20change%20the%20function's%20return%20value%20data%20type%20from%20%3CSTRONG%3EInteger%3C%2FSTRONG%3Eto%20%3CSTRONG%3EString%3C%2FSTRONG%3E%2C%20therefore%2C%20you%20got%20the%20%23VALUE!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824037%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENice%2C%20can%20you%20modify%20this%3C%2FP%3E%3CPRE%3E%3DTEXTJOIN(%22%22%2CTRUE%2CCHAR(%20MOD(MID(A2%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%248%2CLEN(A2)))%2C1)-1%2C10)%2B1%2B96))%3C%2FPRE%3E%3CP%3Eif%20I%20want%20output%20in%20define%20set%20of%20letters%20in%20a%20given%20name%20range%20%22mytable%22%3C%2FP%3E%3CP%3Eeg%20%3A%20cornflakes%26nbsp%3B%20%26nbsp%3Bc%20for%201%2C%20o%20for%202%20and%20so%20on..%3C%2FP%3E%3CP%3Einput%20%3A%20123%20output%20%3A%20cor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emytable%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Einput%26nbsp%3B%3C%2FTD%3E%3CTD%3Eoutput%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Eo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Er%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3En%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3Ef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3El%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3Ek%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3Ee%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3Es%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20the%20one%20I%20am%20using%20but%20how%20to%20make%20it%20reverse%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Ftranslate-letters-to-numbers%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Ftranslate-letters-to-numbers%3C%2FA%3E%26nbsp%3Bi.e%20numbers%20to%20letters%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824221%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398858%22%20target%3D%22_blank%22%3E%40Bhushan_Shiurkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20285px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128688i4BD4ADECB773DD30%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20E2%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%22%2CTRUE%2C%0A%20%20%20%20INDEX(%24B%242%3A%24B%2411%2C%0A%20%20%20%20%20%20%20%20MATCH(%0A%20%20%20%20%20%20%20%20%20%20%20%20MOD(MID(D2%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%2418%2CLEN(D2)))%2C1)-1%2C10)%2B1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%24A%242%3A%24A%2411%2C%0A%20%20%20%20%20%20%20%20%20%20%20%200)%0A))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824367%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824367%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20but%20only%20first%20character%20is%20coming%2C%20I%20downloaded%20your%20file%20there%20also%20only%20first%20character%20is%20coming%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128702iA0EB59500EFA9674%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824390%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398858%22%20target%3D%22_blank%22%3E%40Bhushan_Shiurkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20array%20formula%2C%20you%20shall%20enter%20it%20with%20Ctrl%2BShift%2BEnter%20instead%20of%20Enter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824406%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824406%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20I%20did%20it%20in%20same%20manner%2C%20when%20I%20open%20your%20file%20it%20is%20read%20only%20where%20I%20can%20see%20it%20was%20correct%20the%20moment%20I%20click%20on%20edit%20the%20file%20it%20vanishes%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128706i7F38C5D2B26DE35C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824508%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398858%22%20target%3D%22_blank%22%3E%40Bhushan_Shiurkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry.%20I%20missed%20this%20trick%20with%20N(IF1%2C..).%20Please%20check%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%22%2C1%2C%0A%20%20%20%20VLOOKUP(N(IF(1%2C%0A%20%20%20%20%20%20%20%20--MID(D2%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%2418%2CLEN(D2)))%2C1)%0A%20%20%20%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%20%20%20%20%20%20%24A%242%3A%24B%2411%2C2%2C0%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824562%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat%20it%20is%20working%20!%20but%20when%20I%20tried%20it%20in%20different%20cells%20I%20don't%20know%20why%20it%20is%20not%20working%20I%20changed%20all%20the%20reference%20cell%20addresses..%20It%20is%20working%20only%20at%20A1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128717i356C0746A4B67CDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824730%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sometimes%20use%20%3CSTRONG%3ELOOKUP%3C%2FSTRONG%3Eas%20an%20alternative%20to%20%3CSTRONG%3EINDEX%3C%2FSTRONG%3Ewhen%20coercion%20is%20required.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20CONCAT(%20LOOKUP(%20VALUE(MID(number%2Ck%2C1))%2C%20digits%2C%20characters%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20reverse%20decoding%20and%20I%20do%20not%20have%20to%20convert%20text%20to%20numbers%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20CONCAT(%20LOOKUP(%20MID(text%2C%20k%2C%201)%2C%20array%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20the%20elephant%20in%20that%20room%20is%20that%20I%20have%20had%20to%20sort%20the%20lookup%20data%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIt%20is%20OK%20for%20me%20to%20use%3CFONT%3E'%3CSTRONG%3Earray%3C%2FSTRONG%3E'%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SORT(%20IF(%20%7B0%2C1%7D%2C%20digits%2C%20characters%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20most%20users%20would%20be%20better%20off%20with%20a%20manual%20sort%20using%20a%20helper%20range.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825271%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398858%22%20target%3D%22_blank%22%3E%40Bhushan_Shiurkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20your%20ranges%20the%20formula%20will%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(%22%22%2C1%2C%0A%20%20%20%20VLOOKUP(N(IF(1%2C%0A%20%20%20%20%20%20%20%20--MID(F4%2CROW(%24A%241%3AINDEX(%24A%241%3A%24A%2420%2CLEN(F4)))%2C1)%0A%20%20%20%20%20%20%20%20%20%20%20%20))%2C%0A%20%20%20%20%20%20%20%20%24C%244%3A%24D%2413%2C2%2C0%0A%20%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20point%20is%20that%20the%20part%20of%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EROW(%24A%241%3AINDEX(%24A%241%3A%24A%2420%2CLEN(F4)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20returns%20sequential%20integers%20from%201%20to%20number%20of%20characters%20in%20the%20number%2C%20shall%20always%20start%20from%20row%20%231%20(column%20doesn't%20matter)%20to%20return%20sequence%20like%20%7B1%2C2%2C3%7D.%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20use%20here%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DROW(INDIRECT(%221%3A%22%26amp%3B%20LEN(F4)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20avoid%20side%20effect%20when%20rows%20are%20inserted.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825322%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%2C%20you%20are%20right.%20I%20simply%20forgot%20about%20coercion%20since%20in%20DA%20formula%20works%2C%20after%20that%20add%20correction.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELOOKUP%20with%20SORT%20looks%20great.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825544%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETried%20that%20with%20INDEX%2C%20works%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCONCAT(%0A%20%20INDEX(SORT(MappingRange)%2C%0A%20%20%20%20MID(Number%2CSEQUENCE(LEN(Number))%2C1)%2B1%2C%0A%20%20%20%202)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-826340%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-826340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgreed%2C%20it%20works%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20of%20any%20documentation%20on%20the%20behaviour%20of%20INDEX%20when%20it%20has%20array%20row%20and%20column%20parameters%3F%26nbsp%3B%20Coercion%20was%20always%20something%20of%20a%20black%20art%20but%20now%20the%20rules%20of%20engagement%20appear%20to%20have%20changed.%26nbsp%3B%20The%20truncation%20behaviour%20seems%20to%20depend%20upon%20the%20manner%20in%20which%20the%20formula%20is%20built%20e.g.%20a%20zero%20column%20parameter%20will%20fail%20but%20the%20row%20array%20SEQUENCE(1%2C%20n)%20may%20work%20as%20the%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tend%20to%20view%20filtering%20and%20sorting%20as%20presentational%20devices%20associated%20with%20lists.%26nbsp%3B%20I%20think%20it%20will%20take%20a%20while%20to%20fully%20explore%20the%20extent%20to%20which%20which%20the%20new%26nbsp%3B%3CSPAN%3Efunctions%20%3C%2FSPAN%3E%3CSPAN%3EFILTER%20and%20SORT%3C%2FSPAN%3Ecan%20contribute%20to%20normal%20array%20calculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-828223%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%20-%20nope%2C%20I%20didn't%20see%20such%20documentation.%20That's%20usually%20play%20with%20this%20%2F%20play%20with%20that%20approach.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20checked%20how%20it%20works%20with%20newly%20introduced%20functions.%20As%20expected.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCONCAT(XLOOKUP(--MID(number%2CSEQUENCE(LEN(number))%2C1)%2CInput%2COutput))%0A%0A%3DCONCAT(INDEX(Output%2CXMATCH(--MID(number%2CSEQUENCE(LEN(number))%2C1)%2CInput)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1155616%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1155616%22%20slang%3D%22en-US%22%3E%3CP%3EI%20realize%20this%20thread%20is%20a%20bit%20old%20but..%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20getting%20an%20error%20message%20with%20Haytham's%20formula%20any%20time%20my%20string%20of%20letters%20is%20greater%20than%205.%20I'm%20basically%20trying%20to%20create%20ID%20numbers%20for%20employees%20based%20on%20their%20first%20and%20last%20names.%3CBR%20%2F%3E%3CBR%20%2F%3EJames%20converts%20to%2011451%20-%26gt%3B%20correct%20for%20the%20code%20as%20written.%3CBR%20%2F%3EGeorge%20grants%20an%20error%20message%3CBR%20%2F%3E%3CBR%20%2F%3Eany%20idea%20how%20to%20fix%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162039%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F547410%22%20target%3D%22_blank%22%3E%40JFP-475%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20updated%20the%20code%20in%20the%20attached%20file%20to%20fix%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1275244%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20--%20Converting%20letters%20to%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1275244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20realize%20this%20thread%20is%20a%20bit%20old%20but%E2%80%A6%20would%20like%20to%20make%20a%20joke%20with%20excel.%20Attached%20is%20a%20file%20where%20in%20cell%20A1%20there%20is%20a%20date%20in%20four%20digit%20and%20in%20any%20cell%20where%20if%20Easter(A1)%20is%20written%20then%20appears%20some%20words.%20So%20I%20want%20%26nbsp%3B%22Buona%20Pasqua%20a%20tutto%20il%20forum%22%20has%20to%20appear.%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20your%20attention.%20Regards%3C%2FP%3E%3CP%3ERicky%3C%2FP%3E%3CP%3EXP%20%2B%20Office2003%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, all.  Hoping I could find some help:  I need to convert a string of letters into numbers, and vice versa.  The set-up: the numbers 1,2,3,4,5,6,7,8,9,0 correspond to letters a,b,c,d,e,f,g,h,i,j.  Example: If a user enters hajg in the cell, the formula (or macro) produces 8107.  Or dibe produces 4925.

 

I can have two formulas, so the conversion doesn't have to go both ways in one formula.  I just need to do both.

 

Any ideas out there?

27 Replies
Highlighted

Hello Jason

 

Just a few nested SUBSTITUTE().

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",1),"b",2),"c",3),"d",4),"e",5),"f",6),"g",7),"h",8),"i",9),"j",0)

 

Highlighted

Mr. Lewin

nice formula!

then numbers to letters: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"a"),2,"b"),3,"c"),4,"d"),5,"e"),6,"f"),7,"g"),8,"h"),9,"i"),0,"j")

to extend - add equivalent numbers of "SUBSTITUTE(" to the formula

i.e. there are 10 substitutions - therefore 10 "SUBSTITUTE("

hope I am right... thanks

Highlighted
Best Response confirmed by Jason Summer (New Contributor)
Solution

Hi Jason,

 

If you familiar with VBA in Excel you can create a custom function to do this and use it in the worksheet.

 

This is my suggested function:

Option Compare Text

Function LettersToNumbers(text As String) As Integer
    
    Dim i As Integer
    Dim result As String
    
        For i = 1 To Len(text)
            Select Case Mid(text, i, 1)
             Case "a"
             result = result & 1
             Case "b"
             result = result & 2
             Case "c"
             result = result & 3
             Case "d"
             result = result & 4
             Case "e"
             result = result & 5
             Case "f"
             result = result & 6
             Case "g"
             result = result & 7
             Case "h"
             result = result & 8
             Case "i"
             result = result & 9
             Case "j"
             result = result & 0
            End Select
        Next
        
    LettersToNumbers = result
    
End Function

 

Please follow this link to figure out how to insert this function into your own workbook.

Also, you can place it in the Personal Macro Workbook so that the function will be available in all open workbook and this is described here in this link.

 

However, I've inserted it into the attached workbook.

Hope that helps

Highlighted

Haytham,

 

Thank you!  This is precisely what I was looking for.  I appreciate your help.

 

Regards,

Jason

Highlighted

This works wonderfully!  Thank you for your help!

Highlighted

I was wondering about the numbers to letters -- I figured it was just like your suggestion, but I wasn't sure.  Thank you!

Highlighted

You can use =CODE(UPPER(A1))-64

 

It will work for uppercase and lowercase.

Highlighted

Hello Jason,

I suggest you use https://zetexcel.com/. It has the ability to generate, modify, convert, render and print spreadsheets without using Microsoft Excel

Highlighted

@Haytham Amairah this is brilliant!   However, I couldn't figure out how to do numbers to letters, it show show #VALUE!...

Do you think you could shine some light?


Thank you so much!!!

Highlighted

@Jason Summer 

By the way, formulas could be

text=>number

=--TEXTJOIN("",TRUE,MOD(CODE(LOWER(MID(A1,ROW($A$1:INDEX($A$1:$A$8,LEN(A1))),1)))-96,10))

number=>text

=TEXTJOIN("",TRUE,CHAR( MOD(MID(A2,ROW($A$1:INDEX($A$1:$A$8,LEN(A2))),1)-1,10)+1+96))

clipboard_image_0.png

CSE (array) formula for pre-DA Excel

Highlighted

@jojog

 

To do that, you need to change the above function as follows:

Function LettersToNumbers(text As String) As String
    
    Dim i As Integer
    Dim result As String
    
        For i = 1 To Len(text)
            Select Case Mid(text, i, 1)
             Case 1
             result = result & "a"
             Case 2
             result = result & "b"
             Case 3
             result = result & "c"
             Case 4
             result = result & "d"
             Case 5
             result = result & "e"
             Case 6
             result = result & "f"
             Case 7
             result = result & "g"
             Case 8
             result = result & "h"
             Case 9
             result = result & "i"
             Case 0
             result = result & "j"
            End Select
        Next
        
    LettersToNumbers = result
    
End Function

 

I guess that you have changed it like this but you forgot to change the function's return value data type from Integer to String, therefore, you got the #VALUE! error.

 

Regards

Highlighted

@Sergei Baklan 

Nice, can you modify this

=TEXTJOIN("",TRUE,CHAR( MOD(MID(A2,ROW($A$1:INDEX($A$1:$A$8,LEN(A2))),1)-1,10)+1+96))

if I want output in define set of letters in a given name range "mytable"

eg : cornflakes   c for 1, o for 2 and so on..

input : 123 output : cor

 

mytable

input output
1c
2o
3r
4n
5f
6l
7a
8k
9e
0s

 

this is the one I am using but how to make it reverse

https://exceljet.net/formula/translate-letters-to-numbers i.e numbers to letters

Highlighted

@Bhushan_Shiurkar 

For such sample

image.png

formula in E2 could be

=TEXTJOIN("",TRUE,
    INDEX($B$2:$B$11,
        MATCH(
            MOD(MID(D2,ROW($A$1:INDEX($A$1:$A$18,LEN(D2))),1)-1,10)+1,
            $A$2:$A$11,
            0)
))
Highlighted

Thanks, but only first character is coming, I downloaded your file there also only first character is coming

clipboard_image_0.png

Highlighted

@Bhushan_Shiurkar 

That is array formula, you shall enter it with Ctrl+Shift+Enter instead of Enter.

Highlighted

Yes I did it in same manner, when I open your file it is read only where I can see it was correct the moment I click on edit the file it vanishes

clipboard_image_0.png

Highlighted

@Bhushan_Shiurkar 

Sorry. I missed this trick with N(IF1,..). Please check

=TEXTJOIN("",1,
    VLOOKUP(N(IF(1,
        --MID(D2,ROW($A$1:INDEX($A$1:$A$18,LEN(D2))),1)
            )),
        $A$2:$B$11,2,0
    )
)

 

Highlighted

@Sergei Baklan 

 

Great it is working ! but when I tried it in different cells I don't know why it is not working I changed all the reference cell addresses.. It is working only at A1

 

clipboard_image_0.png

Highlighted

@Sergei Baklan 

I sometimes use LOOKUP as an alternative to INDEX when coercion is required.

= CONCAT( LOOKUP( VALUE(MID(number,k,1)), digits, characters ) )

The reverse decoding and I do not have to convert text to numbers

= CONCAT( LOOKUP( MID(text, k, 1), array ) )

but the elephant in that room is that I have had to sort the lookup data

It is OK for me to use 'array'

= SORT( IF( {0,1}, digits, characters ) )

but most users would be better off with a manual sort using a helper range.