Fórmula genérica
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")
Resumo
Para juntar vários valores de células com uma vírgula, você pode usar uma fórmula baseada nas funções SUBSTITUTE e TRIM. Você pode usar essa mesma abordagem para concatenar valores em células com qualquer delimitador que desejar. No exemplo mostrado, a fórmula em G5 é:
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")
Explicação
Trabalhando de dentro para fora, a fórmula primeiro une os valores das 5 células à esquerda usando o operador de concatenação (&) e um único espaço entre cada valor:
B5&" "&C5&" "&D5&" "&E5&" "&F5
Esta parte da fórmula é irritantemente manual. Para acelerar as coisas, copie & "" & para a área de transferência antes de começar. Em seguida, siga este padrão:
(clique) (colar) (clique) (colar) (clique) (colar)
até chegar à última referência de célula. Na verdade, já passou.
O resultado dessa concatenação (antes da execução de TRIM e SUBSTITUTE) é uma string como esta:
"figs apples "
Em seguida, a função TRIM é usada para "normalizar" todos os espaçamentos. TRIM remove automaticamente o espaço no início e no final de uma determinada string e deixa apenas um espaço entre todas as palavras dentro da string. Isso cuida de espaços extras causados por células vazias.
"figs apples"
Finalmente, SUBSTITUTE é usado para substituir cada espaço ("") por uma vírgula e um espaço (","), retornando o texto como este:
"figs, apples"
Unindo células com outros delimitadores
Para unir células com outro delimitador (separador), basta adaptar o argumento "new_text" dentro de SUBSTITUTE. Por exemplo, para unir células com uma barra, use:
=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")
A saída será semelhante a esta:
limes/apricots/apricots/limes/figs
Função TEXTJOIN
A função TEXTJOIN é uma nova função disponível no Office 365 e Excel 2019. TEXTJOIN permite concatenar um intervalo de células com um delimitador. Com TEXTJOIN, o exemplo acima ficaria assim:
=TEXTJOIN(", ",TRUE,B5:F5)
Macro
Jon Acampora, da ExcelCampus, tem uma macro legal para concatenar um intervalo de células.
