Les subtileses de treballar amb salts de línia a Excel

Salts de línia dins de la mateixa cel·la, afegits mitjançant una drecera de teclat Alt+Enter és una cosa molt comú i habitual. De vegades són fets pels mateixos usuaris per afegir bellesa al text llarg. De vegades, aquestes transferències s'afegeixen automàticament quan es descarreguen dades de qualsevol programa de treball (hola 1C, SAP, etc.) El problema és que no només cal admirar aquestes taules, sinó treballar amb elles, i llavors aquestes transferències de caràcters invisibles poden ser un problema. I és possible que no esdevinguin, si sabeu com manejar-los correctament.

Fem una ullada a aquest problema amb més detall.

Eliminació de salts de línia mitjançant la substitució

Si hem de desfer-nos dels guions, el primer que ens ve al cap és la tècnica clàssica de "trobar i substituir". Seleccioneu el text i, a continuació, truqueu a la finestra de substitució amb una drecera de teclat Ctrl+H o per mitjà de Inici – Cerca i selecciona – Substitueix (Inici — Cerca i selecciona — Substitueix). Una incoherència: no està molt clar com entrar al camp superior Trobar (Trobar què) el nostre personatge invisible de salt de línia. Alt+Enter aquí, malauradament, ja no funciona, copiar aquest símbol directament de la cel·la i enganxar-lo aquí també falla.

Una combinació ajudarà Ctrl+J - aquesta és l'alternativa Alt+Enter als quadres de diàleg o camps d'entrada d'Excel:

Tingueu en compte que després de posar el cursor parpellejant al camp superior i de prémer Ctrl+J – no apareixerà res al camp mateix. No t'espantis, això és normal, el símbol és invisible 🙂

Al camp inferior Suplent (Substituir per) o bé no introduïu res, o bé introduïu un espai (si no només volem eliminar els guions, sinó substituir-los per un espai perquè les línies no s'enganxin en un sol tot). Només cal prémer el botó Substitueix-ho tot (Substitueix-ho tot) i els nostres guions desapareixeran:

ombra: després de realitzar la substitució introduïda amb Ctrl+J El personatge invisible roman al camp Trobar i pot interferir en el futur; no oblideu suprimir-lo col·locant el cursor en aquest camp i diverses vegades (per fiabilitat) prement les tecles Esborrar и Retrocés.

Eliminació de salts de línia amb una fórmula

Si necessiteu resoldre el problema amb fórmules, podeu utilitzar la funció integrada IMPRIMIR (NET), que pot esborrar el text de tots els caràcters no imprimibles, inclosos els nostres accidentats salts de línia:

Aquesta opció, però, no sempre és convenient, perquè les línies després d'aquesta operació es poden enganxar. Per evitar que això passi, no només cal eliminar el guionet, sinó substituir-lo per un espai (vegeu el paràgraf següent).

Substitució dels salts de línia per una fórmula

I si voleu no només suprimir, sinó substituir Alt+Enter en, per exemple, un espai, després caldrà una altra construcció una mica més complexa:

Per establir un guionet invisible fem servir la funció SÍMBOL (CHAR), que genera un caràcter pel seu codi (10). I després la funció SUPLENT (SUBSTITUT) cerca els nostres guions a les dades font i els substitueix per qualsevol altre text, per exemple, amb un espai.

Divisió en columnes per salt de línia

Eina familiar per a molts i molt útil Text per columnes de la pestanya dades (Dades: text a columnes) també pot funcionar molt bé amb salts de línia i dividir el text d'una cel·la en diverses, trencant-lo Alt+Enter. Per fer-ho, al segon pas de l'assistent, heu de seleccionar una variant del caràcter delimitador personalitzat un altre (Personalitzat) i utilitzeu la drecera de teclat que ja coneixem Ctrl+J com a alternativa Alt+Enter:

Si les vostres dades poden contenir diversos salts de línia seguits, podeu "replegar-los" activant la casella de selecció Tracteu els delimitadors consecutius com un sol (Traiteu els delimitadors consecutius com un sol).

Després de fer clic Següent (Pròxim) i seguint els tres passos de l'assistent, obtenim el resultat desitjat:

Tingueu en compte que abans de realitzar aquesta operació, cal inserir un nombre suficient de columnes buides a la dreta de la columna dividida perquè el text resultant no sobreescrigui els valors (preus) que hi havia a la dreta.

Dividiu en línies amb Alt + Enter mitjançant Power Query

Una altra tasca interessant és dividir el text multilínia de cada cel·la no en columnes, sinó en línies:

Es necessita molt de temps fer-ho manualment, és difícil amb les fórmules, no tothom pot escriure una macro. Però a la pràctica, aquest problema es produeix més sovint del que voldríem. La solució més senzilla i senzilla és utilitzar el complement Power Query per a aquesta tasca, que s'ha integrat a Excel des del 2016, i per a les versions anteriors 2010-2013 es pot descarregar completament gratuïtament des del lloc web de Microsoft.

Per carregar les dades d'origen a Power Query, primer heu de convertir-les en una "taula intel·ligent" amb una drecera de teclat Ctrl+T o amb botó Format com a taula llengüeta Home (Inici — Format com a taula). Si per algun motiu no voleu o no podeu utilitzar "taules intel·ligents", podeu treballar amb taules "estúpides". En aquest cas, només cal que seleccioneu l'interval original i doneu-li un nom a la pestanya Fórmules – Gestor de noms – Nou (Fórmules — Gestor de noms — Nou).

Després d'això, a la pestanya dades (si teniu Excel 2016 o posterior) o a la pestanya Power Consulta (si teniu Excel 2010-2013) podeu fer clic al botó De taula/gama (Des de la taula/gama)per carregar la nostra taula a l'editor de Power Query:

Després de carregar, seleccioneu la columna amb text multilínia a les cel·les i seleccioneu l'ordre a la pestanya Principal Dividir la columna: per delimitador (Inici — Dividir la columna — Per delimitador):

Molt probablement, Power Query reconeixerà automàticament el principi de divisió i substituirà el símbol #(lf) caràcter invisible d'avanç de línia (lf = avançament de línia = avançament de línia) al camp d'entrada del separador. Si cal, es poden seleccionar altres caràcters de la llista desplegable a la part inferior de la finestra, si primer marqueu la casella Dividir amb caràcters especials (Dividit per caràcters especials).

Perquè tot estigui dividit en files i no columnes, no us oblideu de canviar el selector Files (Per files) al grup d'opcions avançades.

Només queda fer clic OK i aconsegueix el que vulguis:

La taula acabada es pot descarregar de nou al full mitjançant l'ordre Tancar i carregar: tancar i carregar a... llengüeta Home (Inici — Tancar i carregar — Tancar i carregar a...).

És important tenir en compte que quan utilitzeu Power Query, heu de recordar que quan canvien les dades d'origen, els resultats no s'actualitzen automàticament, perquè. no són fórmules. Per actualitzar, heu de fer clic amb el botó dret a la taula final del full i seleccionar l'ordre Actualitza i desa (Actualització) o premeu el botó Actualitza-ho tot llengüeta dades (Dades — Actualitza-ho tot).

Macro per dividir en línies amb Alt+Enter

Per completar la imatge, esmentem també la solució del problema anterior amb l'ajuda d'una macro. Obriu l'Editor de Visual Basic mitjançant el botó del mateix nom a la pestanya Revelador (Desenvolupador) o dreceres de teclat Alt+F11. A la finestra que apareix, inseriu un mòdul nou a través del menú Inserir – Mòdul i copieu-hi el codi següent:

Sub Split_By_Rows() Dim cel·la Com a rang, n Com a enter Set cel·la = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'determina el nombre de fragments cell.Offset(1, 0) ).Resize(n, 1).EntireRow.Insert 'insereix files buides a sota de la cel·la.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar)' introduïu les dades de la matriu Set cell = cell.Offset(n + 1, 0) 'canvia a la cel·la següent Next i End Sub  

Torneu a Excel i seleccioneu les cel·les amb el text de diverses línies que voleu dividir. A continuació, utilitzeu el botó Macros llengüeta revelador (Desenvolupador - Macros) o drecera de teclat Alt+F8per executar la macro creada, que farà tot el treball per vosaltres:

Voila! Els programadors són, de fet, gent molt mandrosa que preferia treballar dur una vegada i després no fer res 🙂

• Neteja de text de brossa i caràcters addicionals
  
• Substitució de text i eliminació d'espais ininterromputs amb la funció SUBSTITUTE
• Com dividir el text enganxós en parts a Excel