Convert column number to letter

**gtmeloney** · 07-07-2009, 03:59 PM

Is there an easy way (without writing my own conversion function) to get the column letter from a range object, as opposed to the column number?

**Leith Ross** · 07-07-2009, 04:05 PM

Hello gtmeloney,

This requires a Range object abd works with Excel 2000 and up.

  ColLtr = Split(Range("S1").Address(True, False), "$")(0)

**gtmeloney** · 07-07-2009, 04:09 PM

Thanks Leith. Appreciate it.

**Leith Ross** · 07-07-2009, 04:26 PM

Hello gtmeloney,

You're welcome. Thanks for marking the post solved. Have any questions about what the code is doing?

**gtmeloney** · 07-08-2009, 10:48 AM

Hi Leith,

I didn't end up using the code you submitted. I went with the following:

                 strAddr = vRanges(i, j).Address
                 myWs.Cells(nRow, 3) = Mid(strAddr, 2, 1)

vRanges is an array of range objects. I don't know that this code is very sound, but it simply gets the second character from the address string. The address string looks like: $B:$B where B is the column letter that I am interested in.

In your technique, I understand the following:

Range("S1").Address(True, False)

makes the address string look like: S$1.

The "$" is the delimiting character that we need inorder to get the letter out of the range string, so in the example, the 0th index of the resulting array will consist of the "S" while index # 1 will hold "1". Index 0 being the value that I'm interested in.

I wasn't familiar with the split function before your response, does my understanding sound about right?

Thanks again for all of your help

**Leith Ross** · 07-08-2009, 11:13 AM

Hello gtmeloney.

Right you are. A hundred percent spot on. Your code is fine. If you have a large number of conversions (1000 or more) the string functions will slow you down. For a small number of conversions you won't notice in change in performance.