As sutilezas de traballar con saltos de liña en Excel

Saltos de liña dentro da mesma cela, engadidos mediante un atallo de teclado alt+Entrar é algo moi común e común. Ás veces, son feitos polos propios usuarios para engadir beleza ao texto longo. Ás veces, tales transferencias engádense automaticamente cando se descargan datos de calquera programa de traballo (ola 1C, SAP, etc.) O problema é que non só hai que admirar tales táboas, senón tamén traballar con elas, e entón estas transferencias de caracteres invisibles poden ser un problema. E poden non converterse, se sabes como manexalos correctamente.

Vexamos este problema con máis detalle.

Eliminar saltos de liña substituíndo

Se necesitamos desfacernos dos guións, entón o primeiro que adoita ocorrer é a clásica técnica de "buscar e substituír". Seleccione o texto e, a continuación, chame á xanela de substitución cun atallo de teclado Ctrl+H ou a través Inicio - Buscar e seleccionar - Substituír (Inicio — Buscar e seleccionar — Substituír). Unha inconsistencia: non está moi claro como entrar no campo superior Atopar (Atopa o que) o noso personaxe invisible de salto de liña. alt+Entrar aquí, por desgraza, xa non funciona, copiar este símbolo directamente desde a cela e pegalo aquí tamén falla.

Unha combinación axudará Ctrl+J - esa é a alternativa alt+Entrar en caixas de diálogo ou campos de entrada de Excel:

Teña en conta que despois de poñer o cursor parpadeante no campo superior e premer Ctrl+J – non aparecerá nada no propio campo. Non teñas medo: isto é normal, o símbolo é invisible 🙂

Ao campo inferior Suplente (Substituír por) ou ben non introduza nada, ou ben introduza un espazo (se queremos non só eliminar guións, senón substituílos por un espazo para que as liñas non se peguen nun único todo). Só ten que premer o botón Substitúe todo (Substituír todo) e os nosos guións desaparecerán:

sombra: despois de realizar a substitución ingresada con Ctrl+J un personaxe invisible permanece no campo Atopar e pode interferir no futuro - non esqueza borralo colocando o cursor neste campo e premendo varias veces (para fiabilidade) as teclas Borrar и Retroceso.

Eliminar saltos de liña cunha fórmula

Se necesitas resolver o problema con fórmulas, podes usar a función integrada Imprimir (LIMPAR), que pode borrar o texto de todos os caracteres non imprimibles, incluídos os nosos desafortunados saltos de liña:

Esta opción, con todo, non sempre é conveniente, porque as liñas despois desta operación pódense pegar entre si. Para evitar que isto ocorra, cómpre non só eliminar o guión, senón substituílo por un espazo (consulte o seguinte parágrafo).

Substituír saltos de liña por unha fórmula

E se queres non só eliminar, senón substituír alt+Entrar en, por exemplo, un espazo, entón será necesario outra construción un pouco máis complexa:

Para establecer un guión invisible usamos a función SÍMBOLO (CHAR), que saca un carácter polo seu código (10). E despois a función SUBSTITUTO (SUBSTITUTO) busca os nosos guións nos datos de orixe e substitúeos por calquera outro texto, por exemplo, cun espazo.

División en columnas por salto de liña

Ferramenta familiar para moitos e moi útil Texto por columnas dende a pestana Data (Datos: texto a columnas) tamén pode funcionar moi ben con saltos de liña e dividir o texto dunha cela en varias, rompíndoo alt+Entrar. Para iso, no segundo paso do asistente, cómpre seleccionar unha variante do carácter delimitador personalizado Outra (Personalizado) e utiliza o atallo de teclado que xa coñecemos Ctrl+J como alternativa alt+Entrar:

Se os teus datos poden conter varios saltos de liña seguidos, podes "contraelos" activando a caixa de verificación Trata os delimitadores consecutivos como un único (Trata os delimitadores consecutivos como un único).

Despois de premer seguinte (Seguinte) e pasando os tres pasos do asistente, obtemos o resultado desexado:

Teña en conta que antes de realizar esta operación, é necesario inserir un número suficiente de columnas baleiras á dereita da columna dividida para que o texto resultante non sobrescriba os valores (prezos) que estaban á dereita.

Dividir en liñas por Alt + Intro mediante Power Query

Outra tarefa interesante é dividir o texto multiliña de cada cela non en columnas, senón en liñas:

Leva moito tempo facelo manualmente, é difícil coas fórmulas, non todos poden escribir unha macro. Pero na práctica, este problema ocorre con máis frecuencia do que nos gustaría. A solución máis sinxela e doada é utilizar o complemento Power Query para esta tarefa, que está integrado en Excel desde 2016, e para as versións anteriores 2010-2013 pódese descargar de xeito totalmente gratuíto desde o sitio web de Microsoft.

Para cargar os datos de orixe en Power Query, primeiro debes convertelos nunha "táboa intelixente" cun atallo de teclado Ctrl+T ou por botón Formato como táboa aba casa (Inicio — Formato como táboa). Se por algún motivo non queres ou non podes usar "táboas intelixentes", podes traballar con outras "estúpidas". Neste caso, só tes que seleccionar o intervalo orixinal e darlle un nome na pestana Fórmulas – Xestor de nomes – Novo (Fórmulas — Xestor de nomes — Novo).

Despois diso, na pestana Data (se tes Excel 2016 ou posterior) ou na pestana Consulta de enerxía (se tes Excel 2010-2013) podes facer clic no botón Da táboa/rango (De mesa/rango)para cargar a nosa táboa no editor de Power Query:

Despois da carga, seleccione a columna con texto multiliña nas celas e seleccione o comando na pestana Principal Dividir columna: por delimitador (Inicio — Dividir columna — Por delimitador):

Probablemente, Power Query recoñecerá automaticamente o principio de división e substituirá o propio símbolo #(lf) carácter invisible de salto de liña (lf = salto de liña = salto de liña) no campo de entrada do separador. Se é necesario, pódense seleccionar outros caracteres da lista despregable na parte inferior da xanela, se primeiro marca a caixa Dividir con personaxes especiais (Dividido por caracteres especiais).

Para que todo estea dividido en filas e non en columnas, non esquezas cambiar o selector Liñas (Por filas) no grupo de opcións avanzadas.

Só queda premer OK e obtén o que queres:

A táboa rematada pódese descargar de novo na folla usando o comando Pechar e cargar: pechar e cargar en... aba casa (Inicio — Pechar e cargar — Pechar e cargar en…).

É importante ter en conta que ao usar Power Query, debes lembrar que cando os datos de orixe cambian, os resultados non se actualizan automaticamente, porque. non son fórmulas. Para actualizar, debes facer clic co botón dereito sobre a táboa final da folla e seleccionar o comando Actualizar e gardar (Actualizar) ou prema o botón Actualizar todo aba Data (Datos — Actualizar todo).

Macro para dividir en liñas mediante Alt+Intro

Para completar a imaxe, mencionemos tamén a solución do problema anterior coa axuda dunha macro. Abre o Editor de Visual Basic usando o botón do mesmo nome da pestana Revelador (Desenvolvedor) ou atallos de teclado alt+F11. Na xanela que aparece, insira un novo módulo a través do menú Inserir – Módulo e copia alí o seguinte código:

Sub Split_By_Rows() Dim cell As Range, n As Integer Establecer cela = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'determina o número de fragmentos cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'inserir filas baleiras debaixo da cela.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'introduce os datos da matriz Establecer cela = cell.Offset(n + 1, 0) 'mover á seguinte cela Next i End Sub  

Volve a Excel e selecciona as celas co texto multiliña que queres dividir. A continuación, use o botón Macros aba revelador (Desenvolvedor - Macros) ou atallo de teclado alt+F8para executar a macro creada, que fará todo o traballo por ti:

Voila! Os programadores son, de feito, persoas moi preguiceiras que prefiren traballar duro unha vez e despois non facer nada 🙂

• Limpar o texto de lixo e caracteres adicionais
  
• Substitución de texto e eliminación de espazos que non se rompen coa función SUBSTITUTE
• Como dividir texto pegajoso en partes en Excel