Fonction pour convertir le numéro de colonne en lettre?

Quelqu'un a-t-il une fonction Excel VBA qui peut renvoyer la ou les lettres de colonne d'un nombre?

Par exemple, Entrer 100 devrait renvoyer CV.

107
demandé sur ashleedawg 2012-10-09 13:33:40

27 réponses

Cette fonction renvoie la lettre de colonne pour un numéro de colonne donné.

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

Code d'essai pour la colonne 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub
162
répondu brettdj 2017-12-05 19:20:43

Si vous préférez ne pas utiliser un objet range:

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
77
répondu robartsd 2014-08-15 18:22:25

Quelque Chose qui fonctionne pour moi est:

Cells(Row,Column).Address 

Cela renverra la référence de format $ AE $ 1 pour vous.

39
répondu Damian Fennelly 2013-11-21 21:00:41

Et une solution utilisant la récursivité:

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
13
répondu Nikolay Ivanov 2013-11-27 10:31:57

Juste une autre façon de le faire. la réponse de Brettdj m'a fait penser à cela, mais si vous utilisez cette méthode, vous n'avez pas besoin d'utiliser un tableau variant, vous pouvez aller directement à une chaîne.

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

Ou peut le rendre un peu plus compact avec ce

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

Notez que cela dépend de votre référencement de la ligne 1 dans l'objet cells.

13
répondu OSUZorba 2017-12-05 20:07:12

Je suis surpris que personne n'a suggéré de: ** Colonnes(***Index de Colonne***).Adresse **

  • , Par exemple: MsgBox Columns( 9347 ).Address retourne **$à la mère:$MAMAN**.

Pour revenir SEULEMENT le de lettre de colonne(s): Split((Columns(Column Index).Address(,0)),":")(0)

  • , Par exemple: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) retourne ** < code>papa< / code>**.

  D'Autres Exemples


11
répondu ashleedawg 2018-03-30 18:45:37

Ceci est disponible en utilisant une formule:

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

Et ainsi peut également être écrit comme une fonction VBA comme demandé:

Function ColName(colNum As Integer) As String
    ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function
8
répondu Alistair Collins 2014-12-09 12:08:47

Dernière mise à jour: veuillez ignorer la fonction ci-dessous, @SurasinTancharoen a réussi à m'alerter qu'elle est cassée à n = 53.
Pour ceux qui sont intéressés, voici d'autres valeurs brisées juste en dessous de n = 200:

Certaines valeurs de

Veuillez utiliser la fonction @ brettdj pour tous vos besoins. Il fonctionne même pour Microsoft Excel dernier nombre maximum de colonnes limite: {[3] } devrait donne XFD

entrez la description de l'image ici

FIN DE Mise à jour


La fonction ci-dessous est fournie par Microsoft:

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

Source: Comment convertir les numéros de colonne Excel en caractères alphabétiques

S'APPLIQUE À

  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Édition Standard
  • Microsoft Excel 2000 Édition Standard
  • Microsoft Excel 97 Édition Standard
7
répondu mtbink.com 2017-05-23 12:34:38

Ceci est une version de la réponse de robartsd (avec la saveur de la solution d'une ligne de Jan Wijninckx), en utilisant la récursivité au lieu d'une boucle.

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

J'ai testé ceci avec les entrées suivantes:

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""
6
répondu alexanderbird 2017-12-05 20:01:24

Le code de Robertsd est élégant, mais pour le rendre pérenne, changez la déclaration de n en type long

Si vous voulez une formule pour éviter les macros, voici quelque chose qui fonctionne jusqu'à la colonne 702 incluse

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

Où A1 est la cellule contenant le numéro de colonne à convertir en lettres.

6
répondu Jan Wijninckx 2017-12-06 03:50:48

Il existe un moyen très simple d'utiliser Excel power: utilisez la propriété Range.Cells.Address, de cette façon:

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

Cela renverra l'adresse de la colonne souhaitée sur la ligne 1. Prenez-le du 1:

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

Notez qu'il est si rapide et puissant que vous pouvez retourner des adresses de colonne qui existe même!

Remplacez lngRow par le numéro de colonne souhaité en utilisant la propriété Selection.Column!

3
répondu flaviomorgado 2014-07-29 12:47:44

Voici une simple doublure qui peut être utilisée.

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

Cela ne fonctionnera que pour une désignation de colonne de 1 lettre, mais c'est bien pour les cas simples. Si vous en avez besoin pour fonctionner exclusivement avec des désignations de lettres 2, Vous pouvez utiliser ce qui suit:

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
2
répondu Syd B 2014-08-26 14:15:33

Cela fonctionnera quelle que soit la colonne à l'intérieur de votre ligne de code pour la cellule située dans la ligne X, dans la colonne Y:

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

Si vous avez une cellule avec un nom unique défini "Cellname":

Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)
2
répondu Codeplayer 2014-11-05 17:30:41

C'est une fonction basée sur la réponse de @DamienFennelly ci-dessus. Si vous me donnez un pouce vers le haut, donnez-lui un pouce vers le haut aussi! :P

Function outColLetterFromNumber(iCol as Integer) as String
    sAddr = Cells(1, iCol).Address
    aSplit = Split(sAddr, "$")
    outColLetterFromNumber = aSplit(1)
End Function
2
répondu BrettFromLA 2017-12-05 19:02:17

La solution de brettdj fonctionne de manière fantastique, mais si vous rencontrez cela comme une solution potentielle pour la même raison que moi, je pensais que je proposerais ma solution alternative.

Le problème que j'avais était de faire défiler vers une colonne spécifique basée sur la sortie d'une fonction MATCH (). Au lieu de convertir le numéro de colonne en sa lettre de colonne parallèle, j'ai choisi de basculer temporairement le style de référence de A1 à R1C1. De cette façon, je pourrais simplement faire défiler jusqu'au numéro de colonne sans avoir à muck avec une fonction VBA. Pour basculer facilement entre les deux styles de référence, vous pouvez utiliser ce code VBA:

Sub toggle_reference_style()

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

End Sub
1
répondu Will Ediger 2015-02-12 18:07:37

En poursuivant sur la réponse de brettdj, voici pour rendre l'entrée du numéro de colonne facultative. Si l'entrée du numéro de colonne est omise, la fonction renvoie la lettre de colonne de la cellule qui appelle la fonction. Je sais que cela peut également être réalisé en utilisant simplement ColumnLetter(COLUMN()), mais j'ai pensé que ce serait bien s'il pouvait intelligemment le comprendre.

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

Le compromis de cette fonction est qu'elle serait très très légèrement plus lente que la réponse de brettdj à cause du test IF. Mais cela pourrait être ressenti si le fonction est utilisé à plusieurs reprises pour très grande quantité de fois.

1
répondu Rosetta 2016-03-19 04:52:09

Voici une réponse tardive, juste pour une approche simpliste en utilisant Int() et If dans le cas de 1-3 colonnes de caractères:

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
répondu ib11 2016-05-28 21:56:30
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
répondu Krzysztof 2017-03-05 08:35:51

Ici, une fonction simple en 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
répondu Jordi 2017-09-08 11:21:04

Cette formule donnera la colonne basée sur une plage (c'est-à-dire A1), où la plage est une seule cellule. Si une plage de cellules multiples est donnée, elle retournera la cellule en haut à gauche. Notez que les deux références de cellule doivent être identiques:

MI(CELLULE("adresse",A1),2,de RECHERCHE("$",CELLULE("adresse",A1),2)-2)

Comment ça marche:

CELLULE("propriété","plage") renvoie une valeur spécifique de la gamme en fonction de la propriété utilisée. Dans ce cas, l'adresse de la cellule. L'adresse de la propriété renvoie une valeur de $[col]$[ligne], c'est à dire A1 -> $A$1. La fonction MID analyse la valeur de la colonne entre les symboles$.

1
répondu Thom 2018-01-31 19:08:37

Moyen Facile d'obtenir le nom de la colonne

Sub column()

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

End Sub

J'espère que ça aide =)

0
répondu cristobal 2014-11-11 12:09:03
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
répondu Chetan V. 2016-02-04 11:27:19

La Lettre de colonne du numéro de colonne peut être extraite en utilisant la formule en suivant les étapes
1. Calculer l'adresse de la colonne en utilisant la formule D'adresse
2. Extrayez la lettre de colonne en utilisant Mid et FIND function

Exemple:
1. Adresse (1000,1000,1)
résultats $ Tous $ 1000
2. =MID (F15, 2, FIND ( " $ " , F15, 2) -2)
résultats Tous asuming F15 contient le résultat de l'étape 1

En une seule fois, nous pouvons écrire
MID (ADRESSE (1000,1000,1), 2, TROUVER ( " $ " , ADRESSE(1000,1000,1),2)-2)

-1
répondu Bhanu Sinha 2015-09-22 20:54:34

Ceci est seulement pour REFEDIT ... généralement utiliser le code uphere sous peu version... facile à lire et comprendre / il utilise poz de $

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
-1
répondu Gabriel V 2016-03-19 17:10:49

Le Cap A est 65 donc:

MsgBox Chr(ActiveCell.Column + 64)

Trouvés dans: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter

-2
répondu PEDRO COUTO 2016-03-30 09:31:28

Qu'en est-il simplement de convertir en nombre ascii et d'utiliser Chr() pour convertir en une lettre?

Col_letter = Chr (Sélection.Colonne + 96)

-2
répondu beef_supreme 2016-07-15 15:41:06

Voici une autre façon:

{

      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

}
-6
répondu Mike Powell 2014-07-30 10:50:31