SOLVED
Home

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%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%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%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%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
Jason Summer
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?

24 Replies

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)

 

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
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

Haytham,

 

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

 

Regards,

Jason

This works wonderfully!  Thank you for your help!

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

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

 

It will work for uppercase and lowercase.

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

@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!!!

@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

@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

@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

@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)
))

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

clipboard_image_0.png

@Bhushan_Shiurkar 

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

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

@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
    )
)

 

@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

@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.

@Bhushan_Shiurkar 

For your ranges the formula will be

=TEXTJOIN("",1,
    VLOOKUP(N(IF(1,
        --MID(F4,ROW($A$1:INDEX($A$1:$A$20,LEN(F4))),1)
            )),
        $C$4:$D$13,2,0
    )
)

The point is that the part of the formula

ROW($A$1:INDEX($A$1:$A$20,LEN(F4)))

which returns sequential integers from 1 to number of characters in the number, shall always start from row #1 (column doesn't matter) to return sequence like {1,2,3}.

Alternatively you may use here

=ROW(INDIRECT("1:"& LEN(F4)))

to avoid side effect when rows are inserted.

@Peter Bartholomew 

Peter, you are right. I simply forgot about coercion since in DA formula works, after that add correction.

 

LOOKUP with SORT looks great.

 

 

@Peter Bartholomew 

Tried that with INDEX, works as

=CONCAT(
  INDEX(SORT(MappingRange),
    MID(Number,SEQUENCE(LEN(Number)),1)+1,
    2)
)

 

@Sergei Baklan 

Agreed, it works well. 

 

Do you know of any documentation on the behaviour of INDEX when it has array row and column parameters?  Coercion was always something of a black art but now the rules of engagement appear to have changed.  The truncation behaviour seems to depend upon the manner in which the formula is built e.g. a zero column parameter will fail but the row array SEQUENCE(1, n) may work as the parameter.

 

I tend to view filtering and sorting as presentational devices associated with lists.  I think it will take a while to fully explore the extent to which which the new functions FILTER and SORT can contribute to normal array calculation.

 

@Peter Bartholomew 

Peter - nope, I didn't see such documentation. That's usually play with this / play with that approach.

 

By the way, checked how it works with newly introduced functions. As expected.

=CONCAT(XLOOKUP(--MID(number,SEQUENCE(LEN(number)),1),Input,Output))

=CONCAT(INDEX(Output,XMATCH(--MID(number,SEQUENCE(LEN(number)),1),Input)))
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies