Функция для преобразования номера столбца в букву?

Есть ли у кого-нибудь функция Excel VBA, которая может возвращать буквенные буквы из числа?

Например, ввод 100 должен возвращать CV.

Ответ 1

Эта функция возвращает букву столбца для заданного номера столбца.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

код тестирования для столбца 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

Ответ 2

Если вы не хотите использовать объект диапазона:

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

Ответ 3

Что-то, что работает для меня, это:

Cells(Row,Column).Address

Это вернет ссылку формата $AE $1 для вас.

Ответ 4

Например: MsgBox Columns( 9347).Address возвращает .

Чтобы возвращать ТОЛЬКО буквенные ( ).Address(,0)),":")(0): Split((Columns( Column Index ).Address(,0)),":")(0)

Например: MsgBox Split((Columns( 2734).Address(,0)),":")(0) возвращает .

Ответ 5

И решение с использованием рекурсии:

Function ColumnNumberToLetter(iCol As Long) As String

    Dim lAlpha As Long
    Dim lRemainder As Long

    If iCol <= 26 Then
        ColumnNumberToLetter = Chr(iCol + 64)
    Else
        lRemainder = iCol Mod 26
        lAlpha = Int(iCol / 26)
        If lRemainder = 0 Then
            lRemainder = 26
            lAlpha = lAlpha - 1
        End If
        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
    End If

End Function

Ответ 6

Еще один способ сделать это. Brettdj answer заставлял меня думать об этом, но если вы используете этот метод, вам не нужно использовать альтернативный массив, вы можете перейти непосредственно к строке.

ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")

или может сделать его немного более компактным с этим

ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")

Обратите внимание, что это зависит от того, как вы ссылаетесь на строку 1 в объекте ячейки.

Ответ 7

Это доступно с помощью формулы:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

и поэтому также может быть записана как функция VBA по запросу:

Function ColName(colNum As Integer) As String
    ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function

Ответ 8

ПОСЛЕДНЕЕ ОБНОВЛЕНИЕ. Пожалуйста, проигнорируйте приведенную ниже функцию, @SurasinTancharoen удалось предупредить меня, что она разбита на n = 53.
Для тех, кто заинтересован, вот другие сломанные значения чуть ниже n = 200:

Пожалуйста, используйте функцию @brettdj для всех ваших нужд. Он даже работает для максимального максимального количества столбцов Microsoft Excel: 16384 должен указывать XFD

КОНЕЦ ОБНОВЛЕНИЯ

Ниже приведена следующая функция:

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Источник: Как конвертировать номера столбцов Excel в алфавитном порядке

ПРИМЕНЯЕТСЯ НА

Microsoft Office Excel 2007
Стандартная версия Microsoft Excel 2002
Стандартная версия Microsoft Excel 2000
Стандартная версия Microsoft Excel 97

Ответ 9

Это версия ответа robartsd (с ароматом однострочное решение Jan Wijninckx), используя рекурсию вместо цикла.

Public Function ColumnLetter(Column As Integer) As String
    If Column < 1 Then Exit Function
    ColumnLetter = ColumnLetter(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function

Я тестировал это со следующими входами:

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""

Ответ 10

код robertsd является элегантным, но для того, чтобы сделать его перспективным, измените объявление n на тип long

Если вам нужна формула, чтобы избежать макросов, вот что работает до столбца 702 включительно

=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)

где A1 - ячейка, содержащая номер столбца, который должен быть преобразован в буквы.

Ответ 11

Это функция, основанная на @DamienFennely answer выше. Если вы дадите мне большие пальцы, дайте ему большие пальцы!: P

Function outColLetterFromNumber(iCol as Integer) as String
    sAddr = Cells(1, iCol).Address
    aSplit = Split(sAddr, "$")
    outColLetterFromNumber = aSplit(1)
End Function

Ответ 12

Существует очень простой способ использования Excel: используйте свойство Range.Cells.Address, таким образом:

strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)

Это вернет адрес нужного столбца в строке 1. Возьмите его 1:

strCol = Left(strCol, len(strCol) - 1)

Обратите внимание, что это так быстро и мощно, что вы можете вернуть адреса столбцов, которые даже существуют!

Замените lngRow на нужный номер столбца, используя свойство Selection.Column!

Ответ 13

Вот простой лайнер, который можно использовать.

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)

Он будет работать только для обозначения столбца с 1 буквой, но он хорош для простых случаев. Если вам нужно работать только для двух буквенных обозначений, вы можете использовать следующее:

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)

Ответ 14

Это будет работать независимо от того, какой столбец внутри вашей одной строки кода для ячейки, расположенной в строке X, в столбце Y:

Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)

Если у вас есть ячейка с уникальным именем "Cellname":

Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)

Ответ 15

Решение от brettdj работает фантастически, но если вы сталкиваетесь с этим как потенциальное решение по той же причине, что и было, я думал, что предлагаю свое альтернативное решение.

Проблема, с которой я столкнулась, - это прокрутка к определенному столбцу на основе вывода функции MATCH(). Вместо того, чтобы преобразовать номер столбца в параллельную букву столбца, я решил временно переключить стиль ссылки с A1 на R1C1. Таким образом, я мог просто прокрутить до номера столбца, не зациклившись на функции VBA. Чтобы легко переключаться между двумя стилями ссылок, вы можете использовать этот код VBA:

Sub toggle_reference_style()

If Application.ReferenceStyle = xlR1C1 Then
  Application.ReferenceStyle = xlA1
Else
  Application.ReferenceStyle = xlR1C1
End If

End Sub

Ответ 16

Далее, на основе ответа brettdj, нужно сделать ввод номера столбца опционным. Если ввод номера столбца опущен, функция возвращает букву столбца ячейки, которая вызывает функцию. Я знаю, что это также может быть достигнуто с помощью всего лишь ColumnLetter(COLUMN()), но я подумал, что было бы хорошо, если бы оно умело это понимало.

Public Function ColumnLetter(Optional ColumnNumber As Long = 0) As String
    If ColumnNumber = 0 Then
        ColumnLetter = Split(Application.Caller.Address(True, False, xlA1), "$")(0)
    Else
        ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False, xlA1), "$")(0)
    End If
End Function

Компромисс этой функции заключается в том, что она будет очень немного медленнее, чем ответ brettdj из-за теста IF. Но это можно почувствовать, если функция многократно используется в течение очень большого количества раз.

Ответ 17

Вот поздний ответ, просто для упрощенного подхода с использованием Int() и If в случае столбцов с 1 символом:

Function outColLetterFromNumber(i As Integer) As String

    If i < 27 Then       'one-letter
        col = Chr(64 + i)
    ElseIf i < 677 Then  'two-letter
        col = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
    Else                 'three-letter
        col = Chr(64 + Int(i / 676)) & Chr(64 + Int(i - Int(i / 676) * 676) / 26)) & Chr(64 + i - (Int(i - Int(i / 676) * 676) / 26) * 26))
    End If

    outColLetterFromNumber = col

End Function

Ответ 18

Function fColLetter(iCol As Integer) As String
  On Error GoTo errLabel
  fColLetter = Split(Columns(lngCol).Address(, False), ":")(1)
  Exit Function
errLabel:
  fColLetter = "%ERR%"
End Function

Ответ 19

Здесь простая функция в Pascal (Delphi).

function GetColLetterFromNum(Sheet : Variant; Col : Integer) : String;
begin
  Result := Sheet.Columns[Col].Address;  // from Col=100 --> '$CV:$CV'
  Result := Copy(Result, 2, Pos(':', Result) - 2);
end;

Ответ 20

Эта формула даст столбец на основе диапазона (т.е. A1), где диапазон - это одна ячейка. Если задан диапазон нескольких ячеек, он вернет верхнюю левую ячейку. Обратите внимание: обе ссылки на ячейки должны быть одинаковыми:

MID (CELL ( "адрес", A1), 2, SEARCH ( "$", CELL ( "адрес", A1), 2) -2)

Как это устроено:

CELL ("свойство", "диапазон") возвращает определенное значение диапазона в зависимости от используемого свойства. В этом случае адрес ячейки. Свойство address возвращает значение $ [col] $ [row], то есть A1 → $ A $ 1. Функция MID анализирует значение столбца между символами $.

Ответ 21

Простой способ получить имя столбца

Sub column()

cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column

End Sub

Я надеюсь, что это поможет =)

Ответ 22

Sub GiveAddress()
    Dim Chara As String
    Chara = ""
    Dim Num As Integer
    Dim ColNum As Long
    ColNum = InputBox("Input the column number")

    Do
        If ColNum < 27 Then
            Chara = Chr(ColNum + 64) & Chara
            Exit Do
        Else
            Num = ColNum / 26
            If (Num * 26) > ColNum Then Num = Num - 1
            If (Num * 26) = ColNum Then Num = ((ColNum - 1) / 26) - 1
            Chara = Chr((ColNum - (26 * Num)) + 64) & Chara
            ColNum = Num
        End If
    Loop

    MsgBox "Address is '" & Chara & "'."
End Sub

Ответ 23

Так что я опаздываю на вечеринку здесь, но я хочу дать еще один ответ, который еще никто не адресовал, который не включает массивы Вы можете сделать это с помощью простых манипуляций со строками.

Function ColLetter(Col_Index As Long) As String

    Dim ColumnLetter As String

    'Prevent errors; if you get back a number when expecting a letter, 
    '    you know you did something wrong.
    If Col_Index <= 0 Or Col_Index >= 16384 Then
        ColLetter = 0
        Exit Function
    End If

    ColumnLetter = ThisWorkbook.Sheets(1).Cells(1, Col_Index).Address     'Address in $A$1 format
    ColumnLetter = Mid(ColumnLetter, 2, InStr(2, ColumnLetter, "$") - 2)  'Extracts just the letter

    ColLetter = ColumnLetter
End Sub

После ввода в формате $A$1 используйте функцию Mid, начните с позиции 2, чтобы учесть первый $, затем найдите, где второй $ появляется в строке, используя InStr, а затем InStr 2 из, чтобы учесть эта стартовая позиция.

Это дает вам возможность адаптироваться ко всему диапазону возможных столбцов. Поэтому ColLetter(1) возвращает "A", а ColLetter(16384) возвращает "XFD", что является последним возможным столбцом для моей версии Excel.

Ответ 24

Письмо столбца из номера столбца можно извлечь, используя формулу, выполнив следующие шаги: 1. Вычислите адрес столбца с помощью формулы ADDRESS
2. Извлечь букву столбца с помощью функции MID и FIND

Пример:
1. АДРЕС (1000,1000,1)
результаты $ALL $1000
2. = MID (F15,2, FIND ( "$", F15,2) -2)
результаты ALL asuming F15 содержит результат шага 1

За один раз мы можем написать MID (АДРЕС (1000,1000,1), 2, FIND ( "$", адрес (1000,1000,1), 2) -2)

Ответ 25

Это только для REFEDIT... вообще использовать код uphere короткая версия... легко читается и понимается/ он использует poz из $

Private Sub RefEdit1_Change()

    Me.Label1.Caption = NOtoLETTER(RefEdit1.Value) ' you may assign to a variable  var=....'

End Sub

Function NOtoLETTER(REFedit)

    Dim First As Long, Second As Long

    First = InStr(REFedit, "$")                 'first poz of $
    Second = InStr(First + 1, REFedit, "$")     'second poz of $

    NOtoLETTER = Mid(REFedit, First + 1, Second - First - 1)   'extract COLUMN LETTER

End Function

Ответ 26

Крышка A равна 65, поэтому:

MsgBox Chr(ActiveCell.Column + 64)

Найдено в: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter

Ответ 27

как просто преобразовать в число ascii и использовать Chr() для преобразования обратно в букву?

col_letter = Chr (Selection.Column + 96)

Ответ 28

Здесь другой способ:

{

      Sub find_test2()

            alpha_col = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,W,Z" 
            MsgBox Split(alpha_col, ",")(ActiveCell.Column - 1) 

      End Sub

}