worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User
Posted by jpluimers on 2020/03/13
[WayBack] worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User:
Here’s the best I’ve found so far:
=SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")The part in the middle marked in bold is the only part that changes. In this example, it’s taking the current column and adding1, so returningBif it’s in columnAandAAif it’s in columnZ.
It is related to the question and answer [WayBack] Formula to return just the Column Letter in excel – Super User:
FYI on your original formula you don’t actually need to call the CELL formula to get row and column you can use:
=ADDRESS(ROW(),COLUMN())Then as an extension of that you can use MID & SEARCH to find the $ and trim down the output so you are just left with the letter:
=MID(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1,SEARCH("$",ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1)-2)edit You can even simplify this further:
=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)
And it is part of a much more elaborate answer
Here’s the best I’ve found so far:
=SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,””)
The part in the middle marked in bold is the only part that changes.
In this example, it’s taking the current column and adding 1, so returning
Bif it’s in columnAandAAif it’s in columnZ.
Example variants
Example adding to a fixed column (adding 2 to column Z, returns “AB”):
=SUBSTITUTE(ADDRESS(1,( COLUMN(Z1) + 2 ),4),1,””)
Example reading a cell
A1that contains a letter as text to be treated as a column letter:=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,””)
Example reading a cell
A1that contains a number to be treated as a column number (e.g. if it contains 25, it’ll return AB):=SUBSTITUTE(ADDRESS(1,( COLUMN( INDIRECT(A1&1) ) + 1 ),4),1,””)
Looking up cells with
indirect()If your goal is to look up a cell, e.g. using
indirect(), you can skip thesubstitute()and just pipe in the row number. For example, if you want to look up the cell to the right of the current cell (so increasing the cell letter by one and keeping the row the same):=INDIRECT( ADDRESS( ROW(), ( COLUMN() + 1 ),4) )
Based partly on this which has a handy formula for transposing to get a column of column letters, incremented each row.
[WayBack] How do I increment letters in Excel? | Yahoo Answers
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment