wake-up-neo.com

Funktion zur Umwandlung der Spaltennummer in Buchstaben?

Hat jemand eine Excel-VBA-Funktion, die die Spaltenbuchstaben aus einer Zahl zurückgeben kann?

Wenn Sie zum Beispiel 100 eingeben, sollte CV zurückgegeben werden.

excelvbaexcel-vbaexcel-udf
118
mezamorphic

Diese Funktion gibt den Spaltenbuchstaben für eine bestimmte Spaltennummer zurück.

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

Prüfcode für Spalte 100 

Sub Test()
    MsgBox Col_Letter(100)
End Sub
176
brettdj

Wenn Sie kein Bereichsobjekt verwenden möchten:

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
78
robartsd

Was für mich funktioniert, ist:

Cells(Row,Column).Address

Dadurch wird die Referenz für das $ AE $ 1-Format zurückgegeben.

40
Damian Fennelly

 I'm surprised nobody suggested: **<code></code> <code>Columns(</code>***<code>Column Index</code>***<code>).Address</code> <code></code>**

  • Zum BeispielMsgBox Columns( 9347 ).Addressgibt zurück **<code>$MUM:$MUM</code>**.

Um NUR den Spaltenbuchstaben zurückzugeben (s): Split((Columns(Column Index).Address(,0)),":")(0)

  • Zum BeispielMsgBox Split((Columns( 2734 ).Address(,0)),":")(0)gibt zurück **<code>DAD</code>**.

More Examples

15
ashleedawg

Nur noch ein Weg, dies zu tun. Brettdjs Antwort Ich musste daran denken, aber wenn Sie diese Methode verwenden, müssen Sie kein variantes Array verwenden.

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

oder kann es damit etwas kompakter machen

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

Beachten Sie, dass dies davon abhängt, dass Sie Zeile 1 im Zellenobjekt referenzieren.

15
OSUZorba

Und eine Lösung mit Rekursion:

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
14
Nikolay Ivanov

Dies ist über eine Formel verfügbar:

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

und kann auch wie gewünscht als VBA-Funktion geschrieben werden:

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

LATEST UPDATE: Bitte ignorieren Sie die unten stehende Funktion. @SurasinTancharoen hat mich informiert, dass bei n = 53 ein Fehler aufgetreten ist.
Für diejenigen, die daran interessiert sind, sind hier andere gebrochene Werte unterhalb von n = 200:

 Certain values of

Bitte benutzen Sie die @brettdj-Funktion für alle Ihre Bedürfnisse. Es funktioniert sogar für die maximale Anzahl von Spalten in Microsoft Excel: 16384 sollte XFD

 enter image description here

ENDE DER AKTUALISIERUNG

Die folgende Funktion wird von Microsoft bereitgestellt:

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

Quelle: So konvertieren Sie Excel-Spaltennummern in alphabetische Zeichen

GILT FÜR

  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
7
mtbink.com

robertsds Code ist elegant, aber um es zukunftssicher zu machen, ändern Sie die Deklaration von n in type long

Für den Fall, dass Sie eine Formel zur Vermeidung von Makros benötigen, können Sie die Spalte 702 (einschließlich) verwenden

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

dabei ist A1 die Zelle, die die Spaltennummer enthält, die in Buchstaben umgewandelt werden soll.

6
Jan Wijninckx

Dies ist eine Version von robartsds Antwort (mit dem Geschmack von Jan Wijninckxs Einzeilenlösung ), wobei Rekursion anstelle einer Schleife verwendet wird. 

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

Ich habe dies mit den folgenden Eingaben getestet: 

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

Es gibt eine sehr einfache Methode zur Verwendung von Excel-Power: Verwenden Sie die Range.Cells.Address-Eigenschaft auf folgende Weise:

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

Dies gibt die Adresse der gewünschten Spalte in Zeile 1 zurück. Nehmen Sie den 1 mit:

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

Beachten Sie, dass es so schnell und leistungsstark ist, dass Sie Spaltenadressen zurückgeben können, die sogar existieren!

Ersetzen Sie lngRow durch die Selection.Column-Eigenschaft für die gewünschte Spaltennummer!

3
flaviomorgado

Dies ist eine Funktion basierend auf @ DamienFennellys Antwort oben. Wenn du mir einen Daumen nach oben gibst, gib ihm auch einen Daumen nach oben! : P

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

Dies funktioniert unabhängig von der Spalte in Ihrer einen Codezeile für die Zelle, die sich in Zeile X in Spalte Y befindet:

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

Wenn Sie eine Zelle mit einem eindeutigen definierten Namen "Zellname" haben:

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

Hier ist ein einfacher Liner, der verwendet werden kann.

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

Es funktioniert nur für eine 1-Buchstaben-Spaltenbezeichnung, ist aber für einfache Fälle Nizza. Wenn Sie es benötigen, um ausschließlich mit 2 Buchstaben zu arbeiten, können Sie Folgendes verwenden:

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

Diese Formel ergibt die Spalte basierend auf einem Bereich (d.h. A1 ), wobei der Bereich eine einzelne Zelle ist. Wenn ein Bereich mit mehreren Zellen angegeben ist, wird die obere linke Zelle zurückgegeben. Beachten Sie, dass beide Zellverweise gleich sein müssen:

MID (CELL ("Adresse", A1 ), 2, SUCHE ("$", CELL ("Adresse", A1 ), 2) -2)

Wie es funktioniert:

CELL ("property", "range") gibt abhängig von der verwendeten Eigenschaft einen bestimmten Wert des Bereichs zurück. In diesem Fall gibt die Zellenadresse ... die Eigenschaft adresse einen Wert $ [col] $ [Zeile] zurück, d. H. A1 -> $ A $ 1 . Die MID-Funktion analysiert den Spaltenwert zwischen den $ -Symbolen.

1
Thom

Hier ist eine späte Antwort, nur für einen vereinfachten Ansatz, der Int() und If im Fall von 1-3-Zeichen-Spalten verwendet:

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
1
ib11

Die Lösung von brettdj funktioniert fantastisch, aber wenn Sie aus dem gleichen Grund wie ich auf eine mögliche Lösung stoßen, dachte ich, ich würde meine alternative Lösung anbieten.

Das Problem, das ich hatte, war das Scrollen zu einer bestimmten Spalte basierend auf der Ausgabe einer MATCH () - Funktion. Anstatt die Spaltennummer in den Spaltenbuchstaben parallel umzuwandeln, habe ich den Referenzstil vorübergehend von A1 auf R1C1 umgeschaltet. Auf diese Weise konnte ich einfach zur Spaltennummer scrollen, ohne mit einer VBA-Funktion arbeiten zu müssen. Um zwischen den beiden Referenzstilen problemlos umzuschalten, können Sie diesen VBA-Code verwenden:

Sub toggle_reference_style()

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

End Sub
1
Will Ediger

Hier eine einfache Funktion in 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;
1
Jordi

Als Antwort auf die Antwort von brettdj ist hier die Eingabe der Spaltennummer optional. Wenn die Eingabe der Spaltennummer weggelassen wird, gibt die Funktion den Spaltenbuchstaben der Zelle zurück, die die Funktion aufruft. Ich weiß, dass dies auch nur mit ColumnLetter(COLUMN()) erreicht werden kann, aber ich dachte, es wäre schön, wenn es das klug verstehen könnte.

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

Der Kompromiss dieser Funktion ist, dass sie aufgrund des IF-Tests sehr etwas langsamer als brettdjs Antwort wäre. Dies ist jedoch spürbar, wenn die Funktion sehr oft wiederholt verwendet wird.

1
Rosetta
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
1
Krzysztof
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
0
Chetan V.

Also bin ich zu spät zur Party hier, aber ich möchte eine weitere Antwort beisteuern, an die sich bisher noch niemand gerichtet hat, die keine Arrays umfasst. Sie können dies mit einer einfachen String-Manipulation tun.

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

Nachdem Sie die Eingabe im Format $A$1 vorgenommen haben, verwenden Sie die Funktion Mid. Beginnen Sie an Position 2, um den ersten $ zu berücksichtigen. Dann finden Sie, wo der zweite $ in der Zeichenfolge mit InStr angezeigt wird, und subtrahieren Sie 2, um den Start zu berücksichtigen Position.

Dadurch haben Sie den Vorteil, dass Sie für alle möglichen Säulen anpassbar sind. Daher gibt ColLetter(1) "A" zurück und ColLetter(16384) gibt "XFD" zurück, die letzte Spalte für meine Excel-Version.

0
SandPiper

Einfache Möglichkeit, den Spaltennamen zu erhalten

Sub column()

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

End Sub

Ich hoffe es hilft =)

0
cristobal