Table des matières
Sauts de ligne dans la même cellule, ajoutés à l'aide d'un raccourci clavier autre+Entrer est une chose très commune et commune. Parfois, ils sont créés par les utilisateurs eux-mêmes pour ajouter de la beauté à un long texte. Parfois, de tels transferts sont ajoutés automatiquement lors du déchargement de données à partir de programmes de travail (bonjour 1C, SAP, etc.). Le problème est qu'alors vous devez non seulement admirer ces tables, mais travailler avec elles - et ces transferts de caractères invisibles peuvent alors être un problème. Et ils peuvent ne pas devenir – si vous savez comment les gérer correctement.
Examinons ce problème plus en détail.
Suppression des sauts de ligne en remplaçant
Si nous devons nous débarrasser des traits d'union, la première chose qui nous vient généralement à l'esprit est la technique classique « rechercher et remplacer ». Sélectionnez le texte puis appelez la fenêtre de remplacement avec un raccourci clavier Ctrl+H ou par Accueil – Rechercher et sélectionner – Remplacer (Accueil — Rechercher&Sélectionner — Remplacer). Une incohérence - il n'est pas très clair comment entrer dans le champ supérieur Trouver (Trouver quoi) notre caractère de saut de ligne invisible. autre+Entrer ici, malheureusement, cela ne fonctionne plus, copier ce symbole directement depuis la cellule et le coller ici échoue également.
Une combinaison aidera Ctrl+J - c'est l'alternative autre+Entrer dans les boîtes de dialogue ou les champs de saisie Excel :
Veuillez noter qu'après avoir placé le curseur clignotant dans le champ supérieur et appuyé sur Ctrl+J – rien n'apparaîtra dans le champ lui-même. N'ayez pas peur, c'est normal, le symbole est invisible 🙂
Vers le champ du bas Représentant(e) suppléant(e) (Remplacer par) soit ne rien saisir, soit saisir un espace (si l'on veut non seulement supprimer les traits d'union, mais les remplacer par un espace pour que les lignes ne se collent pas en un seul ensemble). Appuyez simplement sur le bouton Tout remplacer (Remplace tout) et nos traits d'union disparaîtront :
Nuance: après avoir effectué le remplacement entré avec Ctrl+J le personnage invisible reste dans le champ Trouver et peut interférer à l'avenir - n'oubliez pas de le supprimer en plaçant le curseur dans ce champ et en appuyant plusieurs fois (pour la fiabilité) sur les touches Supprimer и Retour arrière.
Supprimer les sauts de ligne avec une formule
Si vous avez besoin de résoudre le problème avec des formules, vous pouvez utiliser la fonction intégrée Print (NETTOYER), qui peut effacer le texte de tous les caractères non imprimables, y compris nos malheureux sauts de ligne :
Cette option, cependant, n'est pas toujours pratique, car les lignes après cette opération peuvent être collées ensemble. Pour éviter que cela ne se produise, vous devez non seulement supprimer le trait d'union, mais aussi le remplacer par un espace (voir le paragraphe suivant).
Remplacer les sauts de ligne par une formule
Et si vous voulez non seulement supprimer, mais remplacer autre+Entrer sur un espace par exemple, alors une autre construction un peu plus complexe sera nécessaire :
Pour définir un trait d'union invisible, nous utilisons la fonction SYMBOLE (CARBONISER), qui sort un caractère par son code (10). Et puis la fonction REMPLACER (REMPLAÇANT) recherche nos traits d'union dans les données sources et les remplace par tout autre texte, par exemple par un espace.
Division en colonnes par saut de ligne
Outil familier à beaucoup et très pratique Texte par colonnes de l'onglet Données (Données - Texte vers colonnes) peut également fonctionner très bien avec les sauts de ligne et diviser le texte d'une cellule en plusieurs, en le divisant par autre+Entrer. Pour ce faire, à la deuxième étape de l'assistant, vous devez sélectionner une variante du caractère délimiteur personnalisé Autre (Personnalisé) et utilisez le raccourci clavier que nous connaissons déjà Ctrl+J comme alternative autre+Entrer:
Si vos données peuvent contenir plusieurs sauts de ligne à la suite, vous pouvez les "réduire" en cochant la case Traiter les délimiteurs consécutifs comme un seul (Traiter les délimiteurs consécutifs comme un seul).
Après avoir cliqué sur Suivant (Prochain) et en passant par les trois étapes de l'assistant, nous obtenons le résultat souhaité :
Veuillez noter qu'avant d'effectuer cette opération, il est nécessaire d'insérer un nombre suffisant de colonnes vides à droite de la colonne scindée afin que le texte résultant n'écrase pas les valeurs (prix) qui étaient à droite.
Diviser en lignes par Alt + Entrée via Power Query
Une autre tâche intéressante consiste à diviser le texte multiligne de chaque cellule non pas en colonnes, mais en lignes :
Cela prend beaucoup de temps à faire manuellement, c'est difficile avec les formules, tout le monde ne peut pas écrire une macro. Mais en pratique, ce problème se produit plus souvent que nous ne le souhaiterions. La solution la plus simple et la plus simple consiste à utiliser le complément Power Query pour cette tâche, qui est intégré à Excel depuis 2016, et pour les versions antérieures 2010-2013, il peut être téléchargé entièrement gratuitement sur le site Web de Microsoft.
Pour charger les données sources dans Power Query, vous devez d'abord les convertir en "table intelligente" avec un raccourci clavier Ctrl+T ou par bouton Formater sous forme de tableau languette Accueil (Accueil — Format sous forme de tableau). Si, pour une raison quelconque, vous ne voulez pas ou ne pouvez pas utiliser de « tables intelligentes », vous pouvez travailler avec des « stupides ». Dans ce cas, il suffit de sélectionner la plage d'origine et de lui donner un nom sur l'onglet Formules – Gestionnaire de noms – Nouveau (Formules — Gestionnaire de noms — Nouveau).
Après cela, sur l'onglet Données (si vous avez Excel 2016 ou version ultérieure) ou sur l'onglet Requête d'alimentation (si vous avez Excel 2010-2013) vous pouvez cliquer sur le bouton De table/gamme (À partir du tableau/plage)pour charger notre table dans l'éditeur Power Query :
Après le chargement, sélectionnez la colonne avec du texte multiligne dans les cellules et sélectionnez la commande dans l'onglet Principal Fractionner la colonne - Par délimiteur (Accueil — Diviser la colonne — Par délimiteur):
Très probablement, Power Query reconnaîtra automatiquement le principe de division et remplacera le symbole lui-même #(lf) caractère de saut de ligne invisible (lf = saut de ligne = saut de ligne) dans le champ de saisie du séparateur. Si nécessaire, d'autres caractères peuvent être sélectionnés dans la liste déroulante en bas de la fenêtre, si vous cochez d'abord la case Fractionner avec des caractères spéciaux (Divisé par caractères spéciaux).
Pour que tout soit divisé en lignes et non en colonnes - n'oubliez pas de changer le sélecteur rangées (Par rangées) dans le groupe d'options avancées.
Il ne reste plus qu'à cliquer sur OK et obtenez ce que vous voulez :
Le tableau terminé peut être déchargé sur la feuille à l'aide de la commande Fermer et charger – Fermer et charger en… languette Accueil (Accueil — Fermer&Charger — Fermer&Charger vers…).
Il est important de noter que lorsque vous utilisez Power Query, vous devez vous rappeler que lorsque les données source changent, les résultats ne sont pas automatiquement mis à jour, car. ce ne sont pas des formules. Pour mettre à jour, il faut faire un clic droit sur le tableau final de la feuille et sélectionner la commande Mettre à jour et enregistrer (Rafraîchir) ou appuyez sur le bouton Tout mettre à jour languette Données (Données — Actualiser tout).
Macro de division en lignes par Alt+Entrée
Pour compléter le tableau, mentionnons également la solution du problème précédent à l'aide d'une macro. Ouvrez Visual Basic Editor à l'aide du bouton du même nom de l'onglet Développeur (Développeur) ou raccourcis clavier autre+F11. Dans la fenêtre qui apparaît, insérez un nouveau module via le menu Insertion – Module et copiez-y le code suivant :
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'détermine le nombre de fragments cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'insère des lignes vides sous cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'entre les données du tableau Set cell = cell.Offset(n + 1, 0) 'passe à la cellule suivante Next i End Sub
Revenez à Excel et sélectionnez les cellules contenant le texte multiligne que vous souhaitez diviser. Utilisez ensuite le bouton Macros languette promoteur (Développeur — Macros) ou raccourci clavier autre+F8pour exécuter la macro créée, qui fera tout le travail pour vous :
Voila ! Les programmeurs sont, en fait, des gens très paresseux qui préfèrent travailler dur une fois et ne rien faire 🙂
- Nettoyer le texte des indésirables et des caractères supplémentaires
- Remplacer du texte et supprimer les espaces insécables avec la fonction SUBSTITUTE
- Comment diviser le texte collant en parties dans Excel