INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

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
    ColLtr = Left(Cells(1, rng.Column).Address(False, False), Len(Cells(1, rng.Column).Address(False, False)) - 1)
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close