Excel: Comment composer avec les sauts de lignes?

Publié le 19 mars 2018
par Sophie Marchand M.Sc.
ASCII codes

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.

ASCII codes

 

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”).

INFORMATIONS SYSTEME EXPLOITATION

 

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.

Exemple CAR 10

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:

Sans la mise sous forme de tableau

 

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.

Avec format 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:

Nombre de sauts de lignes

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.

Chercher le saut de ligne

 

Pour trouver le deuxième saut de ligne, on pourrait notamment procéder de la façon suivante:

Trouver 2ième saut de ligne

 

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.

Saut de ligne

 

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.

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.

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.

Saut de ligne

 

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:

Saut de ligne

 

Pour extraire la deuxième expression:
Saut de ligne

 

Pour extraire la troisième expression:
Saut de ligne
Au final, notre tableau ressemblerait à ceci:
Saut de ligne

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.
Table 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”.
Caractères spéciaux Power Query
Vous aurez le choix de plusieurs caractères spéciaux.
Choix 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.
Saut de ligne
Au final, vous obtenez vos 3 colonnes de produits.
Tableau fractionné
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?


 

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 :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Trucs de pro

CFO-Masque_Formations-en-ligne_FBLa 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é.

9 réflexions sur “Excel: Comment composer avec les sauts de lignes?”

  1. Ben Mosbeh Mohamed Slim

    Interressant, moi aussi j’adore power query mais je me demande si c’est la panacée. Exemple comment faire un cumul simple ou conditionnel dans power query ?

  2. Bonjour Sophie
    Une autre possibilité Excel “classique” pour une utilisation ponctuel et donc non Power Query est d’utiliser le menu Données/Conversion/Délimité/Séparateur Autre et mettre ALT + 010
    Cordialement
    Stéphane

  3. Bonjour,
    Sans passer par Power Query, vous pouvez plus simplement utiliser la fonction “Convertir”, du menu “Données” d’Excel. A l’étape 2/3, vous sélectionnez “Séparateur : Autre” et vous tapez ALT + 010.
    C’est plus simple (mais moins marrant) que les grandes formules…
    Merci pour vos tutos,
    Catherine BEAUCLERCQ

    1. Oui… par contre, ce n’est pas automatisé… il faut le refaire à chaque fois… les formules ont au moins l’avantage de pouvoir être utilisées dans une plage de données mise sous forme de tableau et ainsi, automatiser le processus lors de la mise à jour des données…

  4. Bonjour

    En complément la fonction JOINDRE.TEXTE peut être utilisée depuis la version 2016 pour “Créer des sauts de lignes dans une cellule”, elle est beaucoup plus souple que les “&” obligatoires avant cette version.
    – soit en mettant le ALT+ENTREE entre guillement =JOINDRE.TEXTE(”
    “;VRAI;B7:D7)
    – soit avec CAR(10) : =JOINDRE.TEXTE(CAR(10);VRAI;B7:D7)
    – et en tableau : =JOINDRE.TEXTE(CAR(10);VRAI;Tableau1[@[Produit 1]:[Produit 3]])

    Cordialement

    Stéphane

  5. Bonjour, j’ai créé une colonne dont les cellules ont la formule CONCAT sur les 5 colonnes suivantes avec un retour de ligne CAR(10).
    Le résultat est bon mais je voudrais copier / coller cette colonne concaténée pour garder les valeurs et non les formules.
    Quelque soit le copier/coller que j’utilise, les cellules collées reprennent toutes les données mais sur la même ligne. J’ai perdu tous mes retour à la ligne.
    Il y a-t-il une solution svp ?
    Merci.

    1. Bonjour,
      Vous pouvez copier vos cellules, puis en premier lieu coller les valeurs (collage spéciale, valeur), et coller une seconde fois au même endroit en choisissant l’option collage spécial, formats.
      Kim

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut