how do i get word conversion number to text in excel (100 = "hundr

[ismayil]

how do i get word conversion number to text in excel (100 = "hundred")

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.xlFAQ0004.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[YourHelp]

how do i get word conversion number to text in excel (100 = "hundred")

Private Function GetColumnName(ByVal colNumber As Integer) As String
Dim colName As String = String.Empty
Dim quotient As Integer = 0
Dim remainder As Integer = 0

If colNumber <= 26 Then
colName = Convert.ToChar(colNumber + 64)
Return colName
Else
Dim fChar As String = String.Empty
Dim sChar As String = String.Empty

quotient = colNumber \ 26
remainder = colNumber Mod 26
If remainder = 0 Then
fChar = Me.GetColumnName(quotient - 1)
Else
fChar = Me.GetColumnName(quotient)
End If


If remainder = 0 Then
sChar = "Z"
Else
sChar = Me.GetColumnName(remainder)
End If


colName = String.Concat(fChar, sChar)

Return colName
End If

End Function

 

[Chip Pearson]

Private Function GetColumnName(ByVal colNumber As Integer) As String

That can be greatly simplified to:

Function ColLetter(ColNum As Long) As String
ColLetter = Split(Worksheets(1).Cells(1, ColNum).Address(True, False),
"$")(0)
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)