Adding a number to a letter of the alphabet to get a letter
Hi all, I require a function/formula that adds a value to a refernce
to a cell that contains a letter of the alphabet so that the vaue returned is another letter of the alphabet. E.g. If A1 contains "L" , B1 must return "P". This works OK if I do =char(code(A1)+4), but this falls over when I go above 26/Z. I also require the answer "BC" in B1 if the letters contained in A1 are AZ...something along the lines of =function("AZ")+3 returns "BC" where cell A1 contains "AZ" |
Adding a number to a letter of the alphabet to get a letter
Hi all, I require a function/formula that adds a value to a refernce
to a cell that contains a letter of the alphabet so that the vaue returned is another letter of the alphabet. E.g. If A1 contains "L" , B1 must return "P". This works OK if I do =char(code(A1)+4), but this falls over when I go above 26/Z. I also require the answer "BC" in B1 if the letters contained in A1 are AZ...something along the lines of =function("AZ")+3 returns "BC" where cell A1 contains "AZ" Being new to the Excel community, my solutions to problems are apt to be unorthodox. With that said, I think the following worksheet formula will return what you asked for... =LEFT(ADDRESS(1,COLUMN(OFFSET(INDIRECT(A1&"1"), 0,B1)),4),LEN(A1)) Put the "letter(s)" in A1 and the number you want to add to it in B1and the above formula will return the addition you seek. For example, if A1 contains AZ and B1 contains 4, the formula will return BD. Now, out of curiosity, why are you doing this? If it is to move from one location to another, there are more direct ways. Rick |
Adding a number to a letter of the alphabet to get a letter
"Rick Rothstein (MVP - VB)" skrev i en
meddelelse ... Hi all, I require a function/formula that adds a value to a refernce to a cell that contains a letter of the alphabet so that the vaue returned is another letter of the alphabet. E.g. If A1 contains "L" , B1 must return "P". This works OK if I do =char(code(A1)+4), but this falls over when I go above 26/Z. I also require the answer "BC" in B1 if the letters contained in A1 are AZ...something along the lines of =function("AZ")+3 returns "BC" where cell A1 contains "AZ" Being new to the Excel community, my solutions to problems are apt to be unorthodox. With that said, I think the following worksheet formula will return what you asked for... =LEFT(ADDRESS(1,COLUMN(OFFSET(INDIRECT(A1&"1"), 0,B1)),4),LEN(A1)) Put the "letter(s)" in A1 and the number you want to add to it in B1and the above formula will return the addition you seek. For example, if A1 contains AZ and B1 contains 4, the formula will return BD. Now, out of curiosity, why are you doing this? If it is to move from one location to another, there are more direct ways. Rick Another option: =SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+B1, 4),1,"") -- Best regards Leo Heuser Followup to newsgroup only please. |
Adding a number to a letter of the alphabet to get a letter
Being new to the Excel community, my solutions to problems are apt to be
unorthodox. With that said, I think the following worksheet formula will return what you asked for... =LEFT(ADDRESS(1,COLUMN(OFFSET(INDIRECT(A1&"1"), 0,B1)),4),LEN(A1)) Put the "letter(s)" in A1 and the number you want to add to it in B1and the above formula will return the addition you seek. For example, if A1 contains AZ and B1 contains 4, the formula will return BD. Now, out of curiosity, why are you doing this? If it is to move from one location to another, there are more direct ways. Rick Another option: =SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+B1, 4),1,"") Okay, I missed the direct addition... I see I didn't need the OFFSET function call. I'll have to remember that... thanks for posting it. I was going to remark on how much shorter your formula was than mine, but once I removed the OFFSET function, I beat you by a couple of characters.<g =LEFT(ADDRESS(1,B1+COLUMN(INDIRECT(A1&"1")),4),LEN (A1)) Rick |
Adding a number to a letter of the alphabet to get a letter
"Rick Rothstein (MVP - VB)" skrev i en
meddelelse news:% Another option: =SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+B1, 4),1,"") Okay, I missed the direct addition... I see I didn't need the OFFSET function call. I'll have to remember that... thanks for posting it. I was going to remark on how much shorter your formula was than mine, but once I removed the OFFSET function, I beat you by a couple of characters.<g =LEFT(ADDRESS(1,B1+COLUMN(INDIRECT(A1&"1")),4),LEN (A1)) Rick Hey, this should have been the other way round. Me saying, that my formula only has 4 function calls, and you answering: "Yes, but my formula contains fewer characters!" <bg Leo |
Adding a number to a letter of the alphabet to get a letter
Another option:
=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+B1, 4),1,"") Okay, I missed the direct addition... I see I didn't need the OFFSET function call. I'll have to remember that... thanks for posting it. I was going to remark on how much shorter your formula was than mine, but once I removed the OFFSET function, I beat you by a couple of characters.<g =LEFT(ADDRESS(1,B1+COLUMN(INDIRECT(A1&"1")),4),LEN (A1)) Rick Hey, this should have been the other way round. Me saying, that my formula only has 4 function calls, and you answering: "Yes, but my formula contains fewer characters!" <bg Damn! Your formula beat mine in minimum keystroke count also... yours takes 37 keystrokes (including the TAB key presses to select the function from the popup list) whereas mine requires 41 keystrokes.<g Rick |
All times are GMT +1. The time now is 12:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com