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