VBA – Séparer du Texte avec TextToColumns

Ce tutoriel vous montre comment convertir une chaîne de texte dans une seule cellule en plusieurs colonnes à l’aide de la méthode TextToColumns de l’objet Range en VBA

Texte en Colonnes

La méthode Range.TextToColumns en VBA est un outil puissant pour nettoyer les données qui ont été importées de fichiers texte ou csv par exemple.

Considérons la feuille de calcul suivante.

Les données sont entrées dans Excel dans une seule colonne, et sont séparées par des guillemets.

Vous pouvez utiliser la méthode TextToColumns de l’objet Range pour séparer ces données en colonnes distinctes.

Syntaxe de la Fonction TextToColumns

expression.TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

Expression

Il s’agit de la plage de cellules que vous souhaitez diviser, par exemple : Range(« A1:A23 »). Tous les arguments de la méthode TextToColumns sont facultatifs (ils sont entourés de crochets).

Destination

L’endroit où vous voulez que le résultat soit placé – souvent, vous remplacez les données et les fractionnez au même endroit.

DataType

Le type d’analyse de texte que vous utilisez selon le type de données d’entrée – il peut être soit xlDelimited (par défaut si omis), soit xlFixedWidth.

TextQualifier

Si vous avez des guillemets (simples ou doubles) autour de chaque champ du texte que vous divisez, vous devez indiquer s’ils sont simples ou doubles.

ConsecutiveDelimiter

Cette indication est soit vraie, soit fausse et indique à VBA de considérer deux délimiteurs identiques comme s’il s’agissait d’un seul délimiteur.

Tab

Ceci est soit Vrai ou Faux, la valeur par défaut est Faux – ceci indique à VBA que les données sont délimitées par une tabulation.

Semicolon

This is eitherTrue of False, the Default is False – ceci indique à VBA que les données sont délimitées par un point-virgule.

Space

Ceci est soit Vrai ou Faux, la valeur par défaut est Faux – ceci indique à VBA que les données sont délimitées par un espace.

Autre

Cette valeur peut être True ou False, la valeur par défaut étant False. Si vous définissez ce paramètre sur True, l’argument suivant, OtherChar, doit être spécifié.

OtherChar

Il s’agit du caractère par lequel le texte est séparé (par exemple, ^ ou |).

FieldInfo

C’est un tableau contenant des informations sur le type de données qui sont séparées. La première valeur du tableau indique le numéro de colonne dans les données, et la deuxième valeur indique la constante que vous allez utiliser pour représenter le type de données dont vous avez besoin.

Un exemple pour 5 colonnes avec des données de type texte, chiffres et dates pourrait être :

Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlGeneralFormat), Array(4, xlGeneralFormat), Array(5, xlMDYFormat))

Une autre façon de procéder est la suivante :

Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 3))

Les chiffres de la deuxième colonne sont les valeurs des constantes, la constante xlTextFormat ayant une valeur de 2, la constante xlGeneralFormat (par défaut) ayant une valeur de 1 et la constante xlMDYFormat ayant une valeur de 3.

DecimalSeparator

Vous pouvez spécifier le séparateur décimal que VBA doit utiliser si les données contiennent des chiffres. S’il est omis, il utilisera le paramètre système, qui est souvent un point mais peut aussi être une virgule dans les systèmes en français.

ThousandsSeparator

Vous pouvez spécifier le séparateur de milliers que VBA doit utiliser s’il y a des nombres dans les données. S’il est omis, il utilisera le paramètre système, qui est généralement une virgule dans les système configuré en anglais et un espace dans les systèmes en français.

TrailingMinusNumbers

Cet argument est principalement destiné à assurer la compatibilité avec les données générées par d’anciens systèmes où le signe moins était souvent placé après le nombre et non avant. Vous devez définir cet argument sur True si les nombres négatifs ont le signe moins derrière eux. La valeur par défaut est False.

Conversion de Texte en Colonnes

La procédure suivante permet de convertir les données Excel ci-dessus en colonnes.

Sub TexteVersColonnes()

   Sheets("Feuil1").Range("A1:A25").TextToColumns _
   Destination:=Range("A1:A25"), _
   DataType:=xlDelimited, _
   TextQualifier:=xlDoubleQuote, _
   ConsecutiveDelimiter:=True, _
   Tab:=False, _
   Semicolon:=False, _
   Comma:=False, _
   Space:=True, _
   Other:=False, _
   FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
   DecimalSeparator:=".", _
   ThousandsSeparator:=",", _
   TrailingMinusNumbers:=True
   
End Sub

Dans la procédure ci-dessus, nous avons spécifié tous les paramètres. Cependant, de nombreux paramètres sont définis comme faux ou comme paramètres par défaut et ne sont pas nécessaires. Une version plus simple de la procédure ci-dessus est présentée ci-dessous. Vous devez utiliser explicitement les noms des paramètres lorsqu’ils ne sont pas tous spéficiés dans l’ordre attendu.

Sub TexteVersColonnes2()

  Range("A1:A25").TextToColumns _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=True, _
  Space:=True
  
End Sub

Il n’y a que 4 paramètres qui sont réellement requis – les données sont délimitées par un guillemet double, vous voulez que les guillemets consécutifs soient traités comme un seul et les données sont séparées par un espace!

Pour une ligne de code encore plus rapide, nous pourrions omettre les noms des paramètres, mais nous devrions alors mettre des virgules pour sauvegarder la place du paramètre. Vous n’avez besoin de mettre des informations que jusqu’au dernier paramètre que vous utilisez – dans ce cas l’espace qui sépare les données soit est le 8ème paramètre.

Sub TexteVersColonnes3()
   Range("A1:A25").TextToColumns , xlDelimited, xlDoubleQuote, True, , , , True
End Sub

Une fois que vous exécutez l’une des procédures ci-dessus, les données seront séparées comme le montre l’image suivante.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!