5 formas de concatenar datos con un salto de línea en Excel

Mi novia recientemente quiso crear etiquetas de direcciones imprimibles en Excel. Tenía una tabla de información de contacto que incluía el nombre, la dirección, la ciudad, el país y el código postal de cada persona.

El problema era que necesitaba mostrar el nombre, la calle, la ciudad, el país y el código postal, cada uno en una línea separada dentro de una celda.

En esta publicación, veremos cinco formas diferentes en que podemos concatenar los datos y separar cada elemento con saltos de línea. En otras palabras, combinaremos o uniremos datos de varias celdas en una celda y los separaremos con saltos de línea.

En todos estos métodos, nuestros datos están contenidos en una tabla de Excel llamada Contactos.

Descargue el libro de trabajo de muestra para ver todas las soluciones.

Descargar archivo de ejemplo

Concatenar con saltos de línea usando el operador Ampersand

Este es el método más básico, y si tiene una pequeña cantidad de columnas, es fácil de configurar.

La idea es que usaremos el operador ampersand para unir nuestros datos. Esto concatena celdas o cadenas de texto cuando se usa en una fórmula.

También necesitaremos usar la función CHAR . Esta función convierte un número entero del 1 al 255 en un carácter Unicode. De hecho, CHAR(10) devolverá el carácter de salto de línea deseado. Usaremos esto en todos los métodos de fórmula en esta publicación para crear un carácter de salto de línea.

=A2CHAR(10)B2CHAR(10)C2CHAR(10)D2CHAR(10)E2

Esta fórmula es bastante simple. Alterna entre unir un elemento de la dirección a un carácter de salto de línea creado a partir de la función CHAR .

Concatenar con saltos de línea usando la función CONCATENAR o CONCAT

Este método es esencialmente el mismo que el método, pero en su lugar podemos usar las funciones CONCATENAR o CONCAT .

Las funciones CONCATENAR y CONCAT hacen casi exactamente lo mismo, pero CONCAT le permite hacer referencia a un rango en lugar de celdas individuales como la función CONCATENAR . Podemos usar cualquiera ya que nuestra solución solo usará referencias de celdas individuales.

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,CHAR(10),D2,CHAR(10),E2)

Nuevamente, la fórmula alterna entre hacer referencia a los datos y un carácter de salto de línea. Es casi exactamente la misma fórmula si desea utilizar la función CONCAT en su lugar. Simplemente reemplace CONCATENATE con CONCAT .

Concatenar con saltos de línea usando la función TEXTJOIN

Este es otro método de fórmula, pero es la forma preferida si se trata de combinar muchas columnas en una sola celda. Los métodos anteriores se vuelven muy tediosos para escribir la fórmula cuando tenemos más de unas pocas columnas.

La fórmula TEXTJOIN es perfecta para unir datos en un rango y separarlos con un carácter delimitador. En nuestro caso, utilizaremos un carácter de salto de línea como carácter delimitador.

=TEXTJOIN(CHAR(10),TRUE,A2:E2)

Ahora podemos hacer referencia a todos los datos en un rango, en lugar de celdas individuales como las dos fórmulas anteriores. También solo tenemos que definir el delimitador una vez en la fórmula.

Concatenar con saltos de línea usando una Power Query

Power Query es una herramienta de transformación de datos increíble. Hoy en día, cada vez que me encuentro con un problema, siempre pienso en cómo puedo resolverlo usando Power Query.

Dado que este problema es un problema de transformación de datos, Power Query definitivamente puede hacerlo.

Seleccione la tabla de datos de direcciones y vaya a la pestaña Datos y elija el comando Desde tabla/rango .

Necesitamos asegurarnos de que todos los campos sean de tipo texto. Posteriormente los concatenaremos con una función Text.Combine y esto generará un error si alguno de los campos no es de tipo texto.

Observe que el campo de código postal contiene tanto texto como números y, como resultado, se le ha asignado un tipo de datos genérico. Haga clic izquierdo en el icono de tipo de datos y seleccione Texto de las opciones para cambiar esto.

Ahora estamos listos para agregar un nuevo paso para combinar los datos. Haga clic en el ícono fx junto a la barra de fórmulas en el editor de consultas de energía para agregar un nuevo paso. El nuevo paso mostrará la referencia al paso anterior (algo así como #"Changed Type"). Podemos eliminar esto, pero tome nota, ya que deberá usarse en la fórmula que ingresamos.

= Table.AddColumn(#"Changed Type", "Address Labels", each Text.Combine(Record.ToList(_),"#(lf)"))

Pegue la fórmula anterior en la barra de fórmulas y presione Entrar para confirmar el nuevo paso.

Esta fórmula creará una nueva columna en los datos donde cada fila es el resultado de concatenar los datos de las otras columnas con el carácter de salto de línea de consulta de poder #(lf).

Ahora podemos cerrar y cargar los datos desde la pestaña Inicio .

Concatenar con saltos de línea mediante DAX y Power Pivot

Para usar una tabla dinámica para combinar nuestros datos, la necesitaremos en un formato ligeramente diferente. Necesitamos tener todos los datos de la dirección en una columna y otra columna que identifique los datos relacionados entre sí.

Con nuestros datos en este formato, podemos crear una nueva tabla dinámica. Seleccione los datos y vaya a la pestaña Insertar y presione el comando Tabla dinámica .

En el cuadro de diálogo Crear tabla dinámica , asegúrese de marcar la casilla Agregar estos datos al modelo de datos . Esto nos permitirá crear medidas utilizando el lenguaje de fórmula DAX dentro de nuestras tablas dinámicas.

En la ventana Campos de tabla dinámica , haga clic con el botón derecho en el nombre de la tabla y elija Agregar medida .

=CONCATENATEX('ContactList','ContactList'[Address],"")

Agregue un Nombre de medida como Etiqueta, luego ingrese la fórmula anterior para la medida y presione el botón OK .

¿Observe que el carácter de salto de línea necesario en la fórmula de medida es en realidad un salto de línea? Puede crear esto en el editor de fórmulas presionando Enter mientras ingresa una fórmula.

Ahora deberíamos ver un nuevo campo en la ventana Campos de la tabla dinámica. Tendrá un pequeño ícono fx al lado para indicar que es una medida.

Ahora construiremos nuestra tabla dinámica para mostrar la información de la dirección en una celda por persona. Arrastre el campo ID al área Filas y el campo Etiqueta (medida) al área Valores .

Dar formato a los resultados con ajuste de texto

Ok, puede que estés pensando “Hice todos los pasos, pero todavía no veo los resultados. ¡Los datos parecen estar todos en una línea todavía!”.

Confía en mí, los caracteres de salto de línea están ahí. Necesitamos formatear las celdas para ajustar el texto para poder ver los resultados.

Seleccione las celdas que desea formatear y haga clic derecho y elija Formato de celdas de las opciones. También puede presionar Ctrl + 1 en su teclado para abrir el cuadro de diálogo Formato de celdas .

Vaya a la pestaña Alineación y marque el cuadro de texto Ajustar y presione el botón Aceptar .

La celda ahora se mostrará en varias líneas.

Conclusiones

Excel tiene muchas opciones para combinar datos en una sola celda con cada elemento de datos en su propia línea.

La mayoría de las veces, usar una solución basada en fórmulas será la forma más rápida y fácil.

Es posible que nos encontremos en la necesidad de una opción que no se pueda cambiar accidentalmente fácilmente. Power query y power pivot son más adecuados en este caso.

Sea cual sea la solución, siempre es bueno conocer todas las opciones.