Rekha, Sorry, I missed that you wanted to translate all characters to numeric equivalents as in your example.
To do that in a way that will work for a string of any number of characters, I think you need to use a UDF/macro. The worksheet formula approach that comes to mind would involve an array formula, but Excel's text functions don't work with arrays. In this case in particular, it would require, I think, the CONCATENATE() function to take an array argument. Also, you might have a problem with letters above "I", which translates to "9", because the numbers will be two characters long, making, for example, "yes", "bees" and various other combinations all translate to "25519". Rajan gave a working UDF example for you, but he posted it in the "Backup file on every save" thread. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Monday, December 26, 2011 7:08 PM To: excel-macros@googlegroups.com 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
