Excel: Comment composer avec les sauts de lignes?

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.

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

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?

Voyez le tout en action dans ce tutoriel

🎞️ Abonnez-vous à notre chaîne YouTube

Bon visionnement !

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.