We can confidently do this in all cases, because we've hardcoded the row number as 1 inside the ADDRESS function. We are telling SUBSTITUTE to look for '1' and replace it with an empty string (''). For example, assuming we have an address like 'A1', we can use SUBSTITUTE like this: =SUBSTITUTE('A1','1','') // returns 'A' One way to do this is with the SUBSTITUTE function. Now that we have an address with the column reference we want, we simply need to remove the row number. The maximum number of columns in an Excel worksheet is 16,384, so the final column in a worksheet is 'XFD'. In the worksheet shown, we get the column number from column B and use 1 for row number like this: ADDRESS(1,B5,4)Īs the formula is copied down, ADDRESS creates a valid address using each number in column B. We don't particularly care about the row number, we only care about the column number, so we use 1 for row_num in all cases. Note the result from ADDRESS is always a text string. For example: =ADDRESS(1,1) // returns '$A$1'īy providing 4 for the optional abs_num argument, we can get a relative reference: =ADDRESS(1,1,4) // returns 'A1' We can do this with the ADDRESS function, which will return the address for a cell based on a given row and column number.

Working from the inside out, the first step is to construct an address that contains the correct column reference. For reference, the formula in C5 is: =SUBSTITUTE(ADDRESS(1,B5,4),'1','') One way to solve this problem is to construct a valid address with the number and extract just the column from the address. The challenge is that Excel can handle over 16,000 columns, so the number of letter combinations is large.

For example, the number 1 should return 'A', the number 2 should return 'B', the number 26 should return 'Z', etc. In this example, the goal is to convert an ordinary number into a column reference expressed in letters.

Excel convert column to row