In Excel, to find the last column number in the range and then convert the number to column letter, you can use the MIN, COLUMN and COLUMNS functions and nest the formula in the SUBSTITUTE and ADDRESS functions.
I have the following range B1:20 as shown below:
Now in G1, I want to construct a formula that finds the last column number in the range B1:20 and then converts the number to the column letter.
To accomplish the task, I use a formula based on the MIN, COLUMN and COLUMNS functions to find the last column number in the range, like so:
=MIN(COLUMN(B1:D20))+COLUMNS(B1:D20)-1
The formula returns the number 4. To convert the number to the column letter, I use a formula based on the SUBSTITUTE and ADDRESS functions, like so:
=SUBSTITUTE(ADDRESS(1,G1,4),"1","")
The formula returns the letter D. To combine both the formulas into one formula, I replace G1 in the second formula with the first formula, like so:
=SUBSTITUTE(ADDRESS(1,MIN(COLUMN(B1:D20))+COLUMNS(B1:D20)-1,4),"1","")
You have successfully found the last column number in the range and then converted the number to the column letter! I hope this post helped you.
