Fórmula genérica
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")
Resumen
Para unir varios valores de celda con una coma, puede usar una fórmula basada en las funciones SUSTITUIR y RECORTAR. Puede utilizar este mismo enfoque para concatenar valores en celdas con cualquier delimitador que desee. En el ejemplo que se muestra, la fórmula en G5 es:
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")
Explicación
Trabajando de adentro hacia afuera, la fórmula primero une los valores de las 5 celdas a la izquierda usando el operador de concatenación (&) y un solo espacio entre cada valor:
B5&" "&C5&" "&D5&" "&E5&" "&F5
Esta parte de la fórmula es molestamente manual. Para acelerar las cosas, copie & "" & al portapapeles antes de comenzar. Entonces sigue este patrón:
(hacer clic) (pegar) (hacer clic) (pegar) (hacer clic) (pegar)
hasta llegar a la última referencia de celda. En realidad, pasa bastante.
El resultado de esta concatenación (antes de ejecutar TRIM y SUBSTITUTE) es una cadena como esta:
"figs apples "
A continuación, la función TRIM que usamos para "normalizar" todos los espacios. TRIM elimina automáticamente el espacio al principio y al final de una cadena determinada y deja solo un espacio entre todas las palabras dentro de la cadena. Esto se encarga de los espacios adicionales causados por celdas vacías.
"figs apples"
Finalmente, SUBSTITUTE se usa para reemplazar cada espacio ("") con una coma y un espacio (","), devolviendo un texto como este:
"figs, apples"
Unir celdas con otros delimitadores
Para unir celdas con otro delimitador (separador), simplemente adapte el argumento "new_text" dentro de SUBSTITUTE. Por ejemplo, para unir celdas con una barra inclinada, use:
=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")
La salida se verá así:
limes/apricots/apricots/limes/figs
Función TEXTJOIN
La función TEXTJOIN es una nueva función disponible en Office 365 y Excel 2019. TEXTJOIN le permite concatenar un rango de celdas con un delimitador. Con TEXTJOIN, el ejemplo anterior se vería así:
=TEXTJOIN(", ",TRUE,B5:F5)
Macro
Jon Acampora de ExcelCampus tiene una macro genial para concatenar un rango de celdas.
