5 maneiras de concatenar dados com uma quebra de linha no Excel

minha namorada recentemente queria criar etiquetas de endereço impressíveis no Excel. Ela tinha uma tabela de informações de contato, incluindo o nome, endereço de rua, cidade, país e Código postal para cada pessoa.

O problema era que ela precisava exibir o nome, rua, cidade, país e Código Postal cada uma em uma linha separada dentro de uma célula.

Combinado-Dados-em-Um-Celular-com-Quebras de Linha 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

neste post vamos dar uma olhada em cinco maneiras diferentes podemos concatenar os dados e separe cada item com quebras de linha. Em outras palavras, vamos combinar ou juntar dados de várias células em uma célula e separá-los com quebras de linha.

em todos estes métodos, nossos dados estão contidos em uma tabela do Excel chamada Contatos.

baixe o manual de exemplo para ver todas as soluções.

Índice

Concatenar com Quebras de Linha Usando o Operador e Comercial

Este é o método mais básico, e se você tem um pequeno número de colunas, em seguida, é fácil de configurar.

a ideia é que vamos usar o ampersand operator & para juntar os nossos dados. Isto concatenaliza células ou cadeias de texto quando usado em uma fórmula.

também vamos precisar de usar a função CHAR. Esta função converte um número inteiro de 1 a 255 em um caractere unicode. Na verdade, CHAR (10) irá retornar o caráter de quebra de linha desejado. Nós estaremos usando isso em todos os métodos de Fórmula neste post para criar um caractere de quebra de linha.

=A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2

Esta fórmula é bastante simples. Ele alterna entre juntar um item do endereço para um caractere de quebra de linha criado a partir da função CHAR.

Concatenar com Quebras de Linha Usando a CONCATENAR ou Função CONCAT

Este método é essencialmente o mesmo como o & método, mas, em vez disso, podemos usar tanto a CONCATENAR ou CONCAT funções.

CONCATENATE e funções concatenate fazem quase as mesmas coisas, mas CONCAT permite-lhe referenciar um intervalo em vez de células individuais como a função CONCATENATE. Podemos usar qualquer um, uma vez que a nossa solução só irá usar referências de células únicas.

Concatenar-Fórmula 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

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

Novamente, a fórmula alterna entre referenciando os dados e um caractere de quebra de linha. É quase a mesma fórmula se você quiser usar a função CONCAT em vez disso. Substitui a concatenada por CONCAT.

Concatenate with Line Breaks Using the TEXTJOIN Function

This is another formula method, but it’s the preferred way if you’re dealing with combining many columns into one cell. Os métodos anteriores tornam-se muito entediantes para escrever a fórmula quando temos mais do que algumas colunas.

a fórmula TEXTJOIN é perfeita para juntar dados em um intervalo e separá-los com um caractere delimitador. No nosso caso, usaremos um caráter de quebra de linha como o caráter delimitador.

Textjoin-Fórmula 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

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

Agora, podemos fazer referência a todos os dados em um intervalo, em vez de células individuais como os dois anteriores fórmulas. Também só temos de definir o delimitador uma vez na fórmula.

Concatenate with Line Breaks Using a Power Query

Power Query is such a awesome data transformation tool. Hoje em dia, sempre que me deparo com um problema, penso sempre em Como posso resolvê-lo usando a consulta power.

Uma vez que este problema é um problema de transformação de dados, a consulta power pode definitivamente fazê-lo.

Create-Query-from-Table 5 Ways to Concatenate Data with a Line Break in Excel

selecione a tabela de dados de endereço e vá para a Página de dados e escolha o comando Table/Range.

Change-Data-Type-to-Text 5 Ways to Concatenate Data with a Line Break in Excel

We need to make sure all the fields are text type. Mais tarde estaremos a concatená-los com um texto.Combine a função e isto causará um erro se algum dos campos não for o tipo de texto.

Notice that the postal code field contains both text and numbers, and has been given a generic data type as a result. Carregue com o botão esquerdo no ícone do tipo de dados e seleccione o texto das opções para alterar isto.

Adicionar Novo-Consulta-Passo 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

Agora, estamos prontos para adicionar um novo passo para combinar os dados. Carregue no ícone fx ao lado da barra de fórmulas no editor de pesquisas de energia para adicionar um novo passo. O novo passo irá mostrar a referência ao passo anterior (algo como #"Changed Type"). Podemos removê-lo, mas ter em conta que terá de ser usado na fórmula que introduzimos.

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

Cole a fórmula acima na barra de fórmulas e carregue em Enter para confirmar o novo passo.

Esta fórmula irá criar uma nova coluna nos dados onde cada linha é o resultado de concatenar os dados das outras colunas com o carácter de quebra da linha de consulta de potência #(lf).

Agora podemos fechar e carregar os dados da página inicial.

Concatenar com Quebras de Linha Usando DAX e o Poder de articulação

Dados-Formato-de-Pivô-Tabela 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

para usar uma tabela dinâmica para combinar os nossos dados, nós vamos precisar dele em um formato ligeiramente diferente. Precisamos ter todos os dados de endereço em uma coluna e outra coluna que identifica os dados como relacionados uns com os outros.com os nossos dados neste formato, podemos criar uma nova tabela de pivô. Seleccione os dados e vá para a página Inserir e carregue no comando Pivotável.

Adicionar-Mesa-para-os-Dados-Modelo 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

Na caixa de diálogo Criar tabela Dinâmica, certifique-se de verificar a Adicionar dados para o Modelo de Dados de caixa. Isso nos permitirá criar medidas usando a linguagem DAX fomula dentro de tabelas pivot para fora.

Add-A-New-Measure 5 maneiras de concatenar dados com uma quebra de linha no Excel

na janela de Campos Pivotáveis, carregue com o botão direito no nome da tabela e escolha a opção Adicionar medida.

Novo-Medida-Fórmula 5 Maneiras para Concatenar Dados com uma Quebra de Linha no Excel

=CONCATENATEX('ContactList','ContactList',"")

Adicionar um Nome de Medida como a Etiqueta e,em seguida, introduza a fórmula acima, para a medida e pressione o botão OK.

nota que o carácter de quebra de linha necessário na fórmula da medida é de facto uma quebra de linha? Poderá criar isto no editor de fórmulas se carregar em Enter enquanto introduz uma fórmula.

Add-Fields-to-Pivot-Table 5 Ways to Concatenate Data with a Line Break in Excel

We should now see a new field listed in the pivotable Fields window. Ele terá um pequeno ícone fx ao lado dele para indicar que é uma medida.

agora vamos construir a nossa tabela de pivô para mostrar a informação do endereço em uma célula por pessoa. Arraste o campo ID para a área das linhas e o campo legenda (medida) para a área dos valores.

formatação dos resultados com Wrap Text

Ok, você pode estar pensando para si mesmo ” eu fiz todos os passos, mas ainda não estou vendo os resultados. Os dados parecem estar todos numa linha ainda!”.

confie em mim, os caracteres de quebra de linha estão lá. Precisamos formatar as células para embrulhar o texto, a fim de ver os resultados.

Formate-Cells-to-Wrap-Text 5 maneiras de concatenar os dados com uma quebra de linha no Excel

seleccione Todas as células que quiser formatar e carregue com o botão direito e escolha o formato das células das opções. Poderá também carregar em Ctrl + 1 no seu teclado para abrir a janela de formatação das células.

vá para a página de alinhamento e assinale o campo de texto de mudança de linha e carregue no botão OK.

A célula irá agora aparecer em várias linhas.

conclusões

Excel tem muitas opções para combinar dados em uma única célula com cada item de dados em sua própria linha.

A maior parte do tempo usando uma solução baseada em fórmulas será a maneira mais rápida e fácil.

Podemos precisar de uma opção que não pode ser facilmente alterada acidentalmente. Consulta de potência e pivô de potência é mais adequado neste caso.

qualquer que seja a solução, é sempre bom saber sobre todas as suas opções.

Deixe uma resposta

O seu endereço de email não será publicado.