Try this:
Dim intI As Integer For intI = 1 To Len(rngVal.Value) If Asc(UCase(Mid(rngVal.Value, intI, 1))) > 64 And Asc(UCase(Mid(rngVal.Value, intI, 1))) < 91 Then GiveNumeric = GiveNumeric & Asc(UCase(Mid(rngVal.Value, intI, 1))) - 64 End If Next End Function From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of rekha siri Sent: 27 December 2011 11:03 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need help it is resulting wrong result please help for example i gave word abc it should come 123 but it is coming like this 333435 On Tue, Dec 27, 2011 at 11:00 AM, Maries <talk2mar...@gmail.com> wrote: Try this formula... =CONCATENATE(IF(LEN(A1)>=1,CODE(LEFT(A1,1))-64,""),IF(LEN(A1)>=2,CODE(MID(A1 ,2,1))-64,""),IF(LEN(A1)>=3,CODE(MID(A1,3,1))-64,""),IF(LEN(A1)>=4,CODE(MID( A1,4,1))-64,""),IF(LEN(A1)>=5,CODE(MID(A1,5,1))-64,""),IF(LEN(A1)>=6,CODE(MI D(A1,6,1))-64,""),IF(LEN(A1)>=7,CODE(MID(A1,7,1))-64,""),IF(LEN(A1)>=8,CODE( MID(A1,8,1))-64,""),IF(LEN(A1)>=9,CODE(MID(A1,9,1))-64,""),IF(LEN(A1)>=10,CO DE(MID(A1,10,1))-64,""),IF(LEN(A1)>=11,CODE(MID(A1,11,1))-64,""),IF(LEN(A1)> =12,CODE(MID(A1,12,1))-64,""),IF(LEN(A1)>=13,CODE(MID(A1,13,1))-64,""),IF(LE N(A1)>=14,CODE(MID(A1,14,1))-64,""),IF(LEN(A1)>=15,CODE(MID(A1,15,1))-64,"") ,IF(LEN(A1)>=16,CODE(MID(A1,16,1))-64,""),IF(LEN(A1)>=17,CODE(MID(A1,17,1))- 64,""),IF(LEN(A1)>=18,CODE(MID(A1,18,1))-64,""),IF(LEN(A1)>=19,CODE(MID(A1,1 9,1))-64,""),IF(LEN(A1)>=20,CODE(MID(A1,20,1))-64,""),IF(LEN(A1)>=21,CODE(MI D(A1,21,1))-64,""),IF(LEN(A1)>=22,CODE(MID(A1,22,1))-64,""),IF(LEN(A1)>=23,C ODE(MID(A1,23,1))-64,""),IF(LEN(A1)>=24,CODE(MID(A1,24,1))-64,""),IF(LEN(A1) >=25,CODE(MID(A1,25,1))-64,""),IF(LEN(A1)>=26,CODE(MID(A1,26,1))-64,""),IF(L EN(A1)>=27,CODE(MID(A1,27,1))-64,"")) Regards, MARIES. On Tue, Dec 27, 2011 at 8:53 AM, Lakshman Prasad <lakshman...@yahoo.com> wrote: Hello Asa This =FIND(UPPER(A1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ") and this is ok =CODE(UPPER(A1))-64 for under result a =1 b= 2 c=3 but what about for this acb =132 Regards LAKSHMAN From: Asa Rossoff <a...@lovetour.info> To: excel-macros@googlegroups.com Sent: Tuesday, 27 December 2011 8:37 AM Subject: RE: $$Excel-Macros$$ Need help Hi Rekha, Here's one method. It will return 1-26 for A-Z, whether upper or lower case. If any other character, it will give an error: =FIND(UPPER(A1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ") Here's another method, it will return a number for any character. If the charcater is A-Z (upper or lower case), it will be 1-26, otherwise it will be a negative number or a number higher than 26: =CODE(UPPER(A1))-64 Although the above could be modified to return an error for other characters, if that's important, the FIND() method is probably simpler and faster. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of rekha siri Sent: Monday, December 26, 2011 2:01 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need help hi experts, please advise the formula to convert the alphabets to numbers. for example a =1 b= 2 c=3 acb =132 like this for each alphabet it should convert text to number. Thanks for your help Regards, Rekha -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com