Magazine Informatique

Excel: Comment composer avec les sauts de lignes?

Publié le 19 mars 2018 par Sopmar01 @mon_cher_watson

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

Excel: Comment composer avec les sauts de lignes?

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, 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 info@lecfomasque.com


Vous pourriez être intéressé par :

Retour à La Une de Logo Paperblog

Ces articles peuvent vous intéresser :

A propos de l’auteur


Sopmar01 3893 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte