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"

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

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

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

"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

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