Very smart function, in my opinion. Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can’t I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns – maybe more than I need.

I was just going to whip up a short function to do this and post it, but after 10 minutes I still couldn’t do it. Rather than admit defeat, I turned this post into a challenge for you. It’s Friday, you weren’t going to work anyway, were you?

Howsabout a kludgey untested formula for letter to column? (Yes, I did misread the original challenge. Ho hum.)
=IF(LEN(A1)<4,0,26^3*(1+CODE(MID(UPPER(A1),LEN(A1)-3,1))-CODE(“A”)))+IF(LEN(A1)<3,0,26^2*(1+CODE(MID(UPPER(A1),LEN(A1)-2,1))-CODE(“A”)))+IF(LEN(A1)<2,0,26*(1+CODE(MID(UPPER(A1),LEN(A1)-1,1))-CODE(“A”)))+IF(LEN(A1)

I am curious as to why people prefer Harald’s solution to Juan Pablo’s Substitute(ConvertFormula()) one-liner. It would appear the latter would cover *any* number of columns not just restrict itself to those with four letter designations.

To really use a large number of columns — more than 10? :) — one really needs to change the addressing paradigm. I find that I often switch to R1C1 mode when I have to scroll horizontally to see the entire worksheet. Where the f*** is AM? Or GD?

And, that would take care of the need for functions that provide column letters. Wouldn’t it? ;-)

In fact, the A1 and R1C1 conventions are like the QWERTY and Dvorak keyboard layouts or VHS and Betamax. In each case, the latter is/was far superior but the former dominates/won the marketplace.

Application.ScreenUpdating = False ‘Hide screen updates while program is running

Set aWorkbook = ActiveWorkbook ‘Get the active workbook so it can be restored before exiting function
‘Create new workbook so we can be sure we’re looking at a worksheet
Set nWorkbook = Application.Workbooks.Add
nWorkbook.Sheets(1).Activate ‘Activate Sheet1 on the new workbook
‘Don’t pause on error in event that we passed a column that isn’t supported in Excel
On Error Resume Next
colLet = Split(Cells(1, ColumnNumber).Address, “$”) ‘Split Address into array using the “$” as a delimiter
If colLet(1) = “” Then ‘error handler
getColLet = “error”
MsgBox “This version of Excel does not support ” & ColumnNumber & ” Columns”
Else
getColLet = colLet(1)
End If
On Error GoTo 0 ‘Reset error handling to default
nWorkbook.Close (False) ‘Close new workbook without saving
aWorkbook.Activate ‘Activate the previously active workbook

Actually John, this is not specifically limited to 256 columns. Technically it is limited to the max number of columns for the current version of Excel. If a future version of Excel is capable of > 256 columns it won’t return an error this way I can make sure the column really does exist. However I’m not sure if future versions of Excel will be backwards compatible with the code.

Yeah, okay, it’s not limited to 256, it’s limited to the current column count, which is 256. Many of the other approaches don’t have this limit.

I was really more concerned with opening and closing a workbook just to generate a text string. Worksheets.Add would be less of a performance hit, I’m sure. But I’d investigate one of the other approaches. I might even convert to R1C1 notation.

Jon, my previous code had some flaws in it. I believe this code should work, that is unless you have columns in excess of 3.267 Quadrillion :) I think it must be the limit of the double precision number. Note that I had to make my own “mod” function, this is due to the fact that the built in VBA mod function is a long data type and would only support columns up to 32,767.

I’m glad this got resurrected. I just wrote the same function today for probably the 4th time. I thought my latest implementation was slick, but I don’t compare to the one liners. I think Juan Pablo is the winner here. So I feel a little bit better that I can correct even his sweet one line by changing the application.subsitute to a simple replace.

It’s funny how there are so many ways to tackle the same issue. I’ll bet every single one of these approaches differs from the other in execution time by no more than .001 seconds.

need help to figure out formula to make 2 letters equal quantities and then take that value of which ever letter you put in that cell and use it in another formula. (i.e. column A reads 2000 and column B could read “S” which should equal “1? or “P” which should equal “0? then column C takes 2000 times “s”(which equals 1) and calculates 2000 in column C or 2000 times “p” (which equals 0) then it would calculate 0 in column C

Without a UDF, this will work for any value in A1 that represents the number of a single or two character column that is available on your version of Excel, i.e. in 2007 (untested), it should go up to ZZ:
=SUBSTITUTE(LEFT(ADDRESS(1,A1),3),”$”,””)

This is from the original blog article… “The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns – maybe more than I need.” So, no, for that question, your code line would not work.