Contact: 514-605-7112 / info@lecfomasque.com

Excel: Comment composer avec les sauts de lignes?

Publié le : 19 mars 2018

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

 

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?

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Analytique d’affaires et Finance corporative. 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 info@lecfomasque.com

 

Sophie Marchand

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA, d’un titre MVP (Most valuable professional) Excel et d'un titre MVP Data Platform de Microsoft, et cumule de nombreuses années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • réponse 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 ?

  • réponse SL ,

    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

    • réponse Catherine BEAUCLERCQ ,

      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

      • réponse Sophie Marchand ,

        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…

      • réponse SL ,

        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

        • réponse Christophe Tiers ,

          Bonjour,
          Excel autorise l’insertion dans les formules de ALT+ENTREE entre double quotes («  ») avant la version 2016. Exemple :
          =C1& »
          « &C2
          Christophe

          Laisser un commentaire