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

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

Highlighted

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

 

 

Highlighted

@Peter Bartholomew 

Tried that with INDEX, works as

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

 

Highlighted

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

 

Highlighted

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

I realize this thread is a bit old but..

I'm getting an error message with Haytham's formula any time my string of letters is greater than 5. I'm basically trying to create ID numbers for employees based on their first and last names.

James converts to 11451 -> correct for the code as written.
George grants an error message

any idea how to fix?

Highlighted

@JFP-475

 

Hi,

 

I've updated the code in the attached file to fix this issue.

 

Hope that helps

Highlighted

@Haytham Amairah 

Hi,

I realize this thread is a bit old but… would like to make a joke with excel. Attached is a file where in cell A1 there is a date in four digit and in any cell where if Easter(A1) is written then appears some words. So I want  "Buona Pasqua a tutto il forum" has to appear.

Thanks a lot for your attention. Regards

Ricky

XP + Office2003