Converting Numbers to Excel Letter Column vb.net

Question

I am trying to write data to excel files using vb.net. So I my function which converts number column into excel letter columns.

Public Function ConvertToLetter(ByRef iCol As Integer) As String

    Dim Reminder_Part As Integer = iCol Mod 26
    Dim Integer_Part As Integer = Int(iCol / 26)

    If Integer_Part = 0 Then
        ConvertToLetter = Chr(Reminder_Part + 64)
    ElseIf Integer_Part > 0 And Reminder_Part <> 0 Then
        ConvertToLetter = Chr(Integer_Part + 64) + Chr(Reminder_Part + 64)
    ElseIf Integer_Part > 0 And Reminder_Part = 0 Then
        ConvertToLetter = Chr(Integer_Part * 26 + 64)
    End If


End Function

The Function works ok with any other numbers.

For example,

1 => A
2 => B
...
26 => Z
27 => AA
...
51 => AY
52 => t (And here is when it start to went wrong) It is suppose to return AZ, but it returned t.

I couldn't figure out what part I made a mistake. Can someone help me or show me how to code a proper function of converting numbers to excel letter columns using vb.net.

score 11 · Accepted Answer · edited Dec 08 '17 at 20:27

This should do what you want.

Private Function GetExcelColumnName(columnNumber As Integer) As String
    Dim dividend As Integer = columnNumber
    Dim columnName As String = String.Empty
    Dim modulo As Integer

    While dividend > 0
       modulo = (dividend - 1) Mod 26
       columnName = Convert.ToChar(65 + modulo).ToString() & columnName
       dividend = CInt((dividend - modulo) / 26)
   End While

   Return columnName
End Function

Wagner DosAnjos · Answer 2 · 2015-08-12T21:28:34.807

0

There are a couple flaws in the logic, the second else clause is not required and the operations should be zero based.

Public Function ConvertToLetter(ByRef iCol As Integer) As String
    Dim col As Integer = iCol - 1
    Dim Reminder_Part As Integer = col Mod 26
    Dim Integer_Part As Integer = Int(col / 26)

    If Integer_Part = 0 Then
        ConvertToLetter = Chr(Reminder_Part + 65)
    Else
        ConvertToLetter = Chr(Integer_Part + 64) + Chr(Reminder_Part + 65)
    End If


End Function

edited Aug 12 '15 at 21:28

answered Aug 12 '15 at 20:34

Wagner DosAnjos

6,304
1
15
29

Thank you. The code you provide does not work for 26 which should return Z. But it returns AA – bill Aug 12 '15 at 20:49

score 0 · Answer 3 · answered Aug 12 '15 at 20:41

0

This will work up to 52.

Public Function ConvertToLetterA(ByRef iCol As Integer) As String

        Select Case iCol
            Case 1 To 26
                Return Chr(iCol + 64)

            Case 27 To 52
                Return "A" & Chr(iCol - 26 + 64)

        End Select

End Function

On a side note, you can write XLSX files directly with EPPlus via .Net. You can use letter notation for columns if you wish, or you can use numbers.

answered Aug 12 '15 at 20:41

Charles

318
2
7

Thanks. But I am looking for a more general rule of converting numbers to excel columns – bill Aug 12 '15 at 20:51

Converting Numbers to Excel Letter Column vb.net

3 Answers3

Linked