×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Use VBA to convert excel column number to string (label) and reverse function

## Use VBA to convert excel column number to string (label) and reverse function

(OP)

#### CODE --> VBA

Function ConvertToLetter(iCol As Integer) As String
Dim i, j As Integer
Dim sCol As String
Dim iAlpha As Integer
Dim iRemainder As Integer

iRemainder = Int(iCol Mod 26)
If iRemainder = 0 Then
iRemainder = 26
End If

sCol = Chr(iRemainder + 64)
If ((iCol - iRemainder) > 0) And (iCol > 26) Then
sCol = ConvertToLetter(Int((iCol - iRemainder) / 26)) & sCol
End If

ConvertToLetter = sCol
End Function

Function ConvertToInteger(cCol As String) As Integer
Dim iMid, i, j As Integer

cCol = Trim(cCol)
If Len(cCol) > 1 And Len(cCol) < 5 Then
iMid = 0
j = (Len(cCol) - 1)
For i = 1 To (Len(cCol) - 1)
iMid = iMid + (ConvertToInteger(Mid(cCol, i, 1)) * 26 ^ j)
j = j - 1
Next i
ConvertToInteger = iMid + (Asc(UCase(Right(cCol, 1))) - 64)
ElseIf Len(cCol) = 1 And Len(cCol) < 5 Then
ConvertToInteger = Asc(UCase(cCol)) - 64
Else
ConvertToInteger = 0
End If
End Function 

Michael Libeson

### RE: Use VBA to convert excel column number to string (label) and reverse function

Is there a question there?

#### CODE

Function ColNbr(rng As Range) As Integer
ColNbr = rng.Column
End Function

Function ColLtr(rng As Range) As String
End Function

Function Col2Nbr(sCol As String) As Integer
Col2Nbr = ColNbr(Cells(1, sCol))
End Function

Function Col2Ltr(iCol As Integer) As String
Col2Ltr = ColLtr(Cells(1, iCol))
End Function 

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Use VBA to convert excel column number to string (label) and reverse function

(OP)
Just sharing. I had problems with using the address function as it was not available, so I had to come up with another way to convert back and forth between the letter columns and number columns. I tried what others recommended including what was posted on a Microsoft web page, but there were issues with some of the conversions. The one provided appears to work. If anyone runs into any issues using it, please let me know.

Michael Libeson

### RE: Use VBA to convert excel column number to string (label) and reverse function

Huh? Never rralized that.

In what version of Excel is Address not available?

Skip,

Just traded in my OLD subtlety...
for a NUance!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!