Como combinar colunas de endereço no Excel (rua, cidade, estado, ZIP)

UNIRTEXTO (TEXTJOIN no Excel em inglês) é a melhor fórmula para combinar colunas de endereço no Excel 2019, no Excel 2021 e no Microsoft 365. Ela põe um separador entre as partes e ignora células vazias, então uma linha com uma peça faltando não termina com uma vírgula sobrando. As fórmulas desta página usam os nomes de função e o ponto e vírgula do Excel em português; se o seu Excel usa a vírgula como separador de lista, troque cada ; por vírgula. Todos os exemplos usam este layout:

=UNIRTEXTO(", "; VERDADEIRO; B2; C2; D2 & " " & E2)

Isso retorna 88 Pleasant St, Brookline, MA 02446. O estado e o ZIP são unidos antes com um espaço simples, porque os endereços se escrevem MA 02446, não MA, 02446. O VERDADEIRO diz ao UNIRTEXTO para ignorar as células vazias.

Para copiar a fórmula para baixo, clique duas vezes na alça de preenchimento, o quadradinho no canto inferior direito da célula. O Excel preenche até onde a coluna ao lado tiver dados.

Use o e comercial (&), que junta texto em todas as versões do Excel e em qualquer outro app de planilhas. Digite cada separador entre aspas, entre as células.

=B2&", "&C2&", "&D2&" "&E2

CONCATENAR faz o mesmo serviço com ponto e vírgula entre as partes, e continua funcionando em todo lugar. CONCAT, sua substituta mais nova no Excel, também aceita intervalos, mas, ao contrário de UNIRTEXTO, não adiciona separadores, então você continua digitando cada um.

=CONCATENAR(B2; ", "; C2; ", "; D2; " "; E2)
=CONCAT(B2; ", "; C2; ", "; D2; " "; E2)

Adicionar um complemento só quando houver

As fórmulas com e comercial não pulam células vazias. Se algumas linhas têm um apartamento ou uma sala na coluna F, envolva-o em SE para que um complemento vazio não adicione nada. As linhas com complemento ficam 88 Pleasant St Unit 3, Brookline, MA 02446; as demais não mudam.

=B2 & SE(F2 = ""; ""; " " & F2) & ", " & C2 & ", " & D2 & " " & E2

O Excel trata um ZIP como 02446 como o número 2446 e tira o zero. Isso acontece com todo ZIP que começa com 0, o que inclui a Nova Inglaterra, Nova Jersey e Porto Rico. Um mapa ainda pode achar Brookline, MA 2446 pelo nome da cidade, mas um ZIP de quatro dígitos não ajuda, e numa linha sem cidade ele pode falhar de vez. Outras causas de pinos fora do lugar estão em por que os endereços caem no lugar errado.

=UNIRTEXTO(", "; VERDADEIRO; B2; C2; D2 & " " & TEXTO(E2; "00000"))

=SE(NÚM.CARACT(E2) > 5; TEXTO(E2; "00000-0000"); TEXTO(E2; "00000"))

Passe cada parte por ARRUMAR (TRIM em inglês). ARRUMAR remove os espaços no começo e no fim de uma célula e reduz espaços duplos a um só. Eles importam mais do que parece: 88 Pleasant St e 88 Pleasant St podem fazer duas cópias do mesmo endereço parecerem diferentes para uma verificação de duplicados.

=UNIRTEXTO(", "; VERDADEIRO; ARRUMAR(B2); ARRUMAR(C2); ARRUMAR(D2) & " " & TEXTO(E2; "00000"))

Texto copiado de um site ou de um PDF às vezes traz espaços não separáveis, que parecem espaços mas que ARRUMAR deixa passar. Troque-os antes por espaços comuns:

=ARRUMAR(SUBSTITUIR(B2; CARACT(160); " "))

PRI.MAIÚSCULA transforma 88 PLEASANT ST em 88 Pleasant St. É só estética, já que os mapas ignoram maiúsculas, e tem suas manias: NW vira Nw, MCALLISTER vira Mcallister e 5TH AVE vira 5Th Ave. Use para listas impressas e mala direta, não porque um mapa precise.

Combina, com o Preenchimento Relâmpago. Digite à mão o primeiro endereço combinado na coluna ao lado dos dados, exatamente como você quer, e depois pressione Ctrl+E ou escolha Dados → Preenchimento Relâmpago. O Excel percebe o padrão e preenche o resto da coluna com texto simples, não com fórmulas.

• Confira as linhas estranhas. O Preenchimento Relâmpago copia o seu exemplo, então uma linha sem ZIP ou com um complemento extra pode sair errada. Digite um segundo exemplo numa linha diferente e rode de novo.
• Ele não se atualiza. Se você mudar uma rua depois, a coluna combinada fica como estava. As fórmulas acompanham; o Preenchimento Relâmpago não.
• O Google Planilhas tem um parente. O Preenchimento inteligente se oferece para completar a coluna quando percebe um padrão enquanto você digita.

As mesmas fórmulas com UNIRTEXTO e e comercial funcionam no Google Planilhas. Duas diferenças importam: CONCAT no Planilhas junta só dois valores, então use CONCATENAR ou & no lugar, e ARRAYFORMULA pode preencher a coluna inteira a partir de uma única célula. O Planilhas mostra os nomes de função no idioma da planilha; se algum não for reconhecido, use o nome em inglês (TEXTJOIN, SPLIT) ou troque o idioma da planilha.

=ARRAYFORMULA(SE(B2:B = ""; ""; B2:B & ", " & C2:C & ", " & D2:D & " " & E2:E))

A versão em matriz usa e comercial de propósito: UNIRTEXTO dentro de ARRAYFORMULA junta a coluna inteira numa única célula em vez de trabalhar linha por linha. O Planilhas também tem UNIR (JOIN), que funciona como UNIRTEXTO mas não ignora células vazias.

Para fazer o caminho inverso, de uma coluna Endereço para Rua, Cidade, Estado e ZIP, use Dados → Texto para Colunas no Excel: escolha Delimitado, marque Vírgula e conclua. Cada parte separada por vírgula cai na sua própria coluna.

• Antes, insira colunas vazias à direita. Texto para Colunas escreve por cima do que estiver ao lado.
• Estado e ZIP ficam juntos (MA 02446) porque não há vírgula entre eles. Rode Texto para Colunas de novo nessa coluna com Espaço como delimitador e, na última etapa, defina o formato da coluna do ZIP como Texto para os zeros sobreviverem.
• Linhas com um complemento depois de uma vírgula (88 Pleasant St, Unit 3, Brookline) saem com uma coluna a mais que as outras. Filtre pela coluna extra preenchida para encontrá-las.

=DIVIDIRTEXTO(A2; ", ")

=DIVIDIR(A2; ", "; FALSO)

O FALSO importa no Planilhas: sem ele, DIVIDIR corta em toda vírgula e em todo espaço, picando a rua em palavras soltas. Se a lista vai para o planejador de rotas para Excel do EZRoutePlanner, você pode pular os dois sentidos, porque a importação dele lê o endereço esteja ele em uma coluna ou em várias. O passo a passo completo está em como mapear endereços do Excel.