Récemment, un lecteur a posé une question sur notre forum concernant le traitement des sauts de ligne dans Excel. Plus précisément, il cherchait à savoir comment récupérer les différentes informations contenues dans une cellule et affichées avec des sauts de ligne. Cet article vise à vous montrer comment il est possible (mais laborieux, avouons-le…) de traiter ce genre de cas avec des fonctions Excel, notamment avec la fonction CAR (ou CHAR en anglais). Il vise également à vous montrer comment Power Query peut gérer ce genre de situation de façon simple, rapide et exempte d’erreurs. Vous aurez compris que mon choix se tourne sans hésitation vers Power Query pour une situation de ce genre.
La fonction CAR (ou CHAR en anglais)
La fonction CAR repose sur les codes ASCII. Ci-dessous, vous trouverez un tableau des codes ou chiffres que vous pouvez utiliser avec votre fonction CAR dans Excel. Par exemple, si vous jetez un coup d’œil au chiffre 10, vous verrez qu’il est associé à LF (NL Line feed, new line). Il s’agit de notre fonction qui nous permettra de repérer ou de créer des sauts de lignes dans nos cellules.
Fonction CAR selon système d’exploitation
Il faut préciser que la fonction CAR retournera des résultats différents selon que l’usager utilise un Mac ou un PC. Pour retourner les sauts de lignes avec un PC, on doit utiliser la fonction CAR(10) mais avec un Mac, on doit utiliser la fonction CAR(13).
Ce faisant, il pourrait être intéressant d’intégrer un SI dans vos fonctions pour valider d’abord le système d’exploitation de l’usager. Si le système est Mac, vous utilisez CAR(13), sinon, vous utilisez CAR(10).
Pour savoir quel système d’exploitation est utilisé, vous pouvez utiliser la fonction =INFORMATIONS(“SYSTEXPL”).
Créer des sauts de lignes dans une cellule
Si vous souhaitez fusionner les valeurs de différentes cellules et les présenter avec des sauts de lignes, vous devez d’abord modifier le format de votre cellule et sélectionner “Renvoyer à la ligne automatiquement” et vous pouvez ensuite insérer une fonction de concaténation utilisant CAR(10), tel qu’illustré ci-dessous.
La formule ci-dessus est utilisée dans une plage de données qui n’est pas mise sous forme de tableau. Au final, le résultat ressemble à ceci:
L’idéal serait d’utiliser la même formule mais dans une plage de données mise sous forme de tableau. L’écriture de la formule serait alors légèrement modifiée mais plus efficace (une seule formule pour toute la colonne). Toutefois, cet article traitera du sujet sans passer par la mise sous forme de tableau.
Trouver le nombre de sauts de lignes
À l’inverse, si vous souhaitiez trouver le nombre de sauts de lignes dans une cellule, vous pourriez utiliser la formule suivante:
La fonction SUBSTITUE (ou SUBSTITUTE en anglais) remplace les sauts de lignes par rien “”. Autrement dit, elle supprime les sauts de lignes. Ensuite, on compte le nombre de caractères avec les sauts de lignes et le nombre de caractères sans les sauts de lignes et on fait la différence.
Trouver la position d’un saut de ligne
Il existe de nombreuses façons de trouver la position d’un saut de ligne. Pour trouver le premier saut de ligne, on pourrait notamment utiliser une fonction TROUVE (ou FIND en anglais) et chercher la position du saut de ligne. Par défaut, on va obtenir la position du premier saut de ligne.
Pour trouver le deuxième saut de ligne, on pourrait notamment procéder de la façon suivante:
Retourner chacune des expressions séparées par des sauts de lignes
Pour retourner la valeur de la première expression (celle avant le premier saut de ligne), on pourrait simplement utiliser une fonction GAUCHE (ou LEFT en anglais) sur notre cellule de texte et y extirper le nombre de caractères représenté par la position du premier saut de ligne moins 1.
Pour retourner la valeur de la deuxième expression, nous pourrions utiliser la formule ci-dessous. Cette formule remplace d’abord “Table + 1er saut de ligne” par rien “” dans la cellule texte, ce qui nous laisse avec le texte LitArmoire (séparé par un saut de ligne). Par la suite, on procède à nouveau avec une fonction GAUCHE pour extraire le nombre de caractères avant le prochain saut de ligne.
Pour retourner la valeur de la troisième expression, on pourrait utiliser la formule ci-dessous. Cette formule retire d’abord la première expression avec son premier saut de ligne et retire ensuite la deuxième expression avec son deuxième saut de ligne.
Au final, nous serions aux prises avec un tableau qui possèderait quelques erreurs. Bien entendu, nous pourrions tout simplement imbriquer nos formules dans un SIERREUR (ou IFERROR en anglais) mais ceci ne réglerait pas tout et risquerait d’écraser de vraies erreurs au passage.
Nous proposons donc de procéder avec des fonctions SI (ou IF en anglais). Nous pourrions notamment utiliser les fonctions suivantes:
Pour extraire la première expression:
Pour extraire la deuxième expression:
Pour extraire la troisième expression:
Au final, notre tableau ressemblerait à ceci:
Commentaires importants sur la procédure avec fonctions dans Excel
Comme c’est le cas avec toutes les fonctions Texte dans Excel, il existe autant de formules possibles que d’individus et de cas, augmentant tout autant la complexité et les risques d’erreurs. Transformer des valeurs dans Excel par le biais de fonctions texte laborieuses peut être éviter en utilisant Power Query. Je l’ai mentionné fréquemment sur ce blogue et je le répète encore, Power Query est la meilleure chose qui soit arrivée à Excel depuis sa naissance. Et vous n’avez plus d’excuses pour ne pas l’utiliser. Power Query est disponible en téléchargement libre pour Excel 2010 et Excel 2013 et se retrouve intégré dans toutes les versions d’Excel 2016 (mais il ne porte pas le nom de Power Query, ses fonctionnalités sont imbriquées dans le menu Données).
Procédure allégée avec Power Query
Importez d’abord votre table dans Power Query.
Utilisez la fonctionnalité “Fractionner la colonne par délimiteur”.
Sélectionnez “Personnalisé” puis ouvrez le menu des options avancées pour cocher la case “Diviser en utilisant des caractères spéciaux”.
Vous aurez le choix de plusieurs caractères spéciaux.
Choisissez le caractère spécial “Saut de ligne” et vous verrez apparaître #(lf) comme délimiteur personnalisé. Il s’agit du symbole qui représente les sauts de lignes.
Au final, vous obtenez vos 3 colonnes de produits.
Vous serez d’accord avec moi que cette procédure est beaucoup plus simple et beaucoup plus robuste que la précédente, n’est-ce pas?
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre un vaste choix de formations Excel, notamment en tableaux de bord et en modélisation financière, ainsi qu’une série de formations en lien avec Power BI. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel. De plus, nos formations peuvent mener à l’obtention d’une accréditation.
Découvrez quelles formations vous conviennent
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble