The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,978 other followers

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 adding 1, so returning B if it’s in column A and AA if it’s in column Z.

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 B if it’s in column A and AA if it’s in column Z.


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 A1 that 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 A1 that 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 the substitute() 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: