Home |
Search |
Today's Posts |
#2
|
|||
|
|||
=char(a1+64)
where A1 hold the value 4 "Gary Brown" m wrote in message ... Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ |
#3
|
|||
|
|||
This is from Chip Pearson:
Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(True, False), 1 - (ColNumber 26)) End Function And as a worksheet function (with the number in A1): =SUBSTITUTE(ADDRESS(1,A1,4),"1","") Gary Brown wrote: Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ -- Dave Peterson |
#4
|
|||
|
|||
Brilliant :O
Thanks. -- Gary Brown Please rate this posting if it is helpful to you. "N Harkawat" wrote: =char(a1+64) where A1 hold the value 4 "Gary Brown" m wrote in message ... Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ |
#5
|
|||
|
|||
I knew there had to be a better way.
Leave it to Chip. Thanks! -- Gary Brown Please rate this posting if it is helpful to you. "Dave Peterson" wrote: This is from Chip Pearson: Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(True, False), 1 - (ColNumber 26)) End Function And as a worksheet function (with the number in A1): =SUBSTITUTE(ADDRESS(1,A1,4),"1","") Gary Brown wrote: Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ -- Dave Peterson |
#6
|
|||
|
|||
Nice! I like the worksheet formula. I've seen some long ugly formula to
convert numbers to column letters and vice-versa. Jason "Dave Peterson" wrote: This is from Chip Pearson: Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(True, False), 1 - (ColNumber 26)) End Function And as a worksheet function (with the number in A1): =SUBSTITUTE(ADDRESS(1,A1,4),"1","") Gary Brown wrote: Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ -- Dave Peterson |
#7
|
|||
|
|||
Remember that will only work if you are talking about columns A:Z. It will
fail on AA and beyond. On Fri, 10 Jun 2005 06:42:04 -0700, "Gary Brown" m wrote: Brilliant :O Thanks. |
#8
|
|||
|
|||
That's pretty high praise coming from you. Thanks.
Or even a blind squirrel finds a nut now and then! Jason Morin wrote: Nice! I like the worksheet formula. I've seen some long ugly formula to convert numbers to column letters and vice-versa. Jason "Dave Peterson" wrote: This is from Chip Pearson: Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(True, False), 1 - (ColNumber 26)) End Function And as a worksheet function (with the number in A1): =SUBSTITUTE(ADDRESS(1,A1,4),"1","") Gary Brown wrote: Does anyone know of an easy way of getting the Column Letter if you know the Column Number? ie: a function that returns "D" if I input 4. I've written a UDF but is there a better way? I've listed my UDF below. TIA, -- Gary Brown '/==============================================/ Private Function ColumnLetterFromNumber(iColNumber As Long) _ As String 'this function converts column number into letters 'this is designed to only work thru ZZ (702 columns), ' however, currently Excel only goes to IV (256 columns) 'Syntax: =ColumnLetterFromNumber(4) ' returns 'D' 'Gary Brown 05/11/2000 Dim str1stLetter As String, str2ndLetter As String Application.Volatile True ColumnLetterFromNumber = "" On Error GoTo err_Function If iColNumber <= 26 Then str1stLetter = "" Else str1stLetter = Chr(Int(iColNumber / 26.001) + 64) End If str2ndLetter = _ Chr((iColNumber - _ (26 * Int(iColNumber / 26.001))) + 64) ColumnLetterFromNumber = str1stLetter & str2ndLetter exit_Function: Exit Function err_Function: Debug.Print "Error: " & Err.Number & " - " & Err.Description ColumnLetterFromNumber = "" GoTo exit_Function End Function '/==============================================/ -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add row total when cell contains both number and letter . | Excel Discussion (Misc queries) | |||
How do I sort a column a unique number? | Excel Worksheet Functions | |||
Count number of occurences in 1 column only if something in anothe | Excel Worksheet Functions | |||
how do I find an average number of specific words in a column | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |