Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

【R1C1→A1用】Excelの列番号を英文字に変換する【VB.NET】

Introduction


Excel の列番号を英文字に変換する方法 | Microsoft Docs

を自分用にVB.NETでちょい編集。

諸事情でR1C1形式が使えなく、仕方なしでA1形式に変換しなければいけないので、、、

Program

Program.vb
Imports System

Module Program
    Sub Main(args As String())
        ' 標準入力
        Console.Write("Please Input x : ")
        Dim x AS Decimal = Console.ReadLine()

        ' 変換して標準出力
        Console.WriteLine(ConvertToLetter(x))
    End Sub


    ' Excel の列番号を英文字に変換する
    ' 例) 1 -> A , 26 -> Z , 29 -> AC
    ' https://docs.microsoft.com/ja-jp/office/troubleshoot/excel/convert-excel-column-numbers
    Function ConvertToLetter(iCol As Decimal) As String
        Dim a As Decimal
        Dim b As Decimal
        a = iCol
        ConvertToLetter = String.Empty

        ' iCol が1未満の場合、終了
        Do While iCol > 0
            a = Int((iCol - 1) / 26)    ' (iCol-1) を 26で除算した商
            b = (iCol - 1) Mod 26       ' (iCol-1) を 26で除算した余り

            ' b を 対応するアルファベット文字 ( 0 => A , 25 => Z )に変換し、結果を文字列の先頭に付加
            ConvertToLetter = Chr(b + 65) & ConvertToLetter

            ' iCal に a を代入してループ
            iCol = a
        Loop
    End Function
End Module

Output

image.png

Takuro_K
なんちゃってプログラマです。 過去の備忘録にでもしようかな。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away