como dividir texto string no Excel por vírgula, espaço, caractere ou máscara
o tutorial explica como dividir células no Excel usando fórmulas e a funcionalidade de texto dividido. Você aprenderá como separar texto por vírgula, espaço ou qualquer outro delimitador, e como dividir strings em texto e números.
dividir texto de uma célula em várias células é a tarefa que todos os usuários do Excel estão lidando de vez em quando., Em um dos nossos artigos anteriores, discutimos como dividir células no Excel usando o texto para a funcionalidade coluna, preenchimento Flash e divisão de nomes add-in. Hoje, vamos dar uma olhada em profundidade em como você pode dividir strings usando fórmulas e a funcionalidade de texto dividido.,
- Como dividir células no Excel como utilizar fórmulas
- cadeia de caracteres de Divisão por vírgula, dois pontos, barra, traço ou outro delimitador
- Dividir a seqüência de caracteres de quebra de linha
- Fórmulas para dividir o texto e números
- Como células separadas com a Divisão recurso de Texto
- Dividir células por caractere
- Dividir a célula a string
- Dividir células por máscara (padrão)
Como dividir o texto no Excel como utilizar fórmulas
Para dividir uma seqüência de caracteres no Excel, você geralmente usa a ESQUERDA, para a DIREITA ou para a função de meio, em combinação com a LOCALIZAR ou PESQUISAR., À primeira vista, algumas das fórmulas podem parecer complexas, mas a lógica é de fato bastante simples, e os seguintes exemplos lhe darão algumas pistas.
cadeia dividida por vírgula, cólon, barra, traço ou outro delimitador
ao dividir as células no Excel, a chave é localizar a posição do delimitador dentro da cadeia de texto. Dependendo da sua tarefa, isto pode ser feito usando a pesquisa sem distinção de maiúsculas ou a procura com distinção de maiúsculas. Uma vez que você tenha a posição do delimitador, use a função direita, esquerda ou meio para extrair a parte correspondente da cadeia de texto., Para uma melhor compreensão, vamos considerar o seguinte exemplo.
Supondo que você tem uma lista de SKUs do Item-Cor-Tamanho padrão, e você deseja dividir a coluna em 3 colunas separadas:
Como dividir seqüência de caracteres de quebra de linha no Excel
Para dividir o texto por espaço, uso de fórmulas semelhantes àquelas demonstrado no exemplo anterior. A única diferença é que você vai precisar da função CHAR para fornecer o caractere de quebra de linha, uma vez que você não pode digitá-lo diretamente na fórmula.,
Supondo, as células que você deseja dividir semelhante a este:
Tire as fórmulas do exemplo anterior e substituir um hífen (“-“) com o CHAR(10), onde 10 é o código ASCII para a Linha de alimentação.
And this is how the result looks like:
How to split text and numbers in Excel
To begin with, there is no universal solution that would work for all alfanumeric strings. Qual a fórmula a usar depende do padrão de string em particular. Abaixo você encontrará as fórmulas para os dois cenários comuns.,
cadeia dividida de” texto + número ” padrão
supondo, você tem uma coluna de cadeias com texto e números combinados, onde um número sempre segue texto. Você quer quebrar as cadeias originais para que o texto e os números apareçam em células separadas, como este:
o resultado pode ser alcançado de duas maneiras diferentes.,
Método 1: contar os dígitos e extrair que muitos caracteres
a maneira mais fácil de dividir a cadeia de texto onde o número vem depois do texto é esta:
para extrair os números, você procura o texto para cada número possível de 0 a 9, obtém o total dos números, e devolve que muitos caracteres do fim da cadeia.,
com a cadeia original em A2, a fórmula é a seguinte:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
para extrair texto, calcula quantos caracteres de texto a cadeia contém subtraindo o número de dígitos extraídos (C2) do comprimento total da cadeia original em A2. Depois disso, você usa a função esquerda para retornar que muitos caracteres do início da string.,
=LEFT(A2,LEN(A2)-LEN(C2))
Onde A2 é a seqüência de caracteres original e C2 é o extraídos número, como mostrado na imagem:
Método 2: Descubra a posição do 1º dígito em uma seqüência de caracteres
Uma solução alternativa seria utilizar a seguinte fórmula para determinar a posição do primeiro dígito da seqüência de caracteres:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
A explicação detalhada da fórmula”s lógica pode ser encontrada aqui.,
Uma vez que a posição do primeiro dígito seja encontrada, você pode dividir texto e números usando fórmulas muito simples esquerda e direita.
Para extrair texto:
=LEFT(A2, B2-1)
Para extrair número:
=RIGHT(A2, LEN(A2)-B2+1)
Onde A2 é a seqüência de caracteres original e B2 é a posição do primeiro número., o primeiro dígito, você pode incorporar o MIN fórmula para a ESQUERDA e para a DIREITA funções:
Fórmula para extrair texto:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
Fórmula para extrair números:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
cadeia de caracteres de Divisão do “número + texto” padrão
Se você está dividindo células onde o texto aparece após o número, você pode extrair números com a seguinte fórmula:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
A fórmula é semelhante ao que vimos no exemplo anterior, exceto que você use a função para a ESQUERDA em vez da DIREITA para obter o número a partir do lado esquerdo da cadeia.,
uma Vez que os números, extrair o texto subtraindo-se o número de dígitos a partir do comprimento total da cadeia de caracteres original:
=RIGHT(A2,LEN(A2)-LEN(B2))
Onde A2 é a seqüência de caracteres original e B2 é o extraídos número, como mostrado na imagem abaixo:
É assim que você pode dividir strings no Excel usando diferentes combinações de diferentes funções., Como você vê, as fórmulas estão longe de ser óbvias, então você pode querer baixar a amostra Excel Split Cells workbook para examiná-las mais de perto.
Se descobrir o arcano torções de fórmulas do Excel, não é sua ocupação favorita, você pode gostar do visual método para dividir células no Excel, o que é demonstrado na próxima parte deste tutorial.,
Como dividir células no Excel com a Divisão recurso de Texto
Uma forma alternativa para dividir uma coluna no Excel é usando o Split recurso de Texto incluídos com o nosso melhor Suite do Excel, que fornece as seguintes opções:
- Dividir células por caractere
- Dividir a célula a string
- Dividir células por máscara (padrão)
Para tornar as coisas mais claras, vamos dar uma olhada em cada opção, uma de cada vez.
dividir as células por carácter
escolha esta opção sempre que quiser dividir o conteúdo da célula em cada ocorrência do carácter indicado.,
para este exemplo, vamos ” s the take The strings of the Item-Color-Size pattern that we used in the first part of this tutorial. Como devem lembrar-se, separámo-los em três colunas diferentes, usando três fórmulas diferentes. E aqui”é como você pode alcançar o mesmo resultado em 2 passos rápidos:
- assumindo que você tem o pacote final instalado, selecione as células a dividir, e clique no ícone de texto dividido na página de dados Ablebits.,
- A Divisão painel de Texto será aberto no lado direito da janela do Excel, e você fazer o seguinte:
- Expandir a Divisão por grupo de caracteres, e selecione uma das predefinidos delimitadores do tipo ou qualquer outro caractere na caixa Personalizada.
- escolha se deve dividir as células em colunas ou linhas.
- reveja o resultado sob a secção de antevisão e carregue no botão Dividir.
dividir as células por string
Esta opção permite-lhe dividir strings usando qualquer combinação de caracteres como delimitador. Tecnicamente, você divide uma cadeia em partes usando uma ou várias substrings diferentes como os limites de cada parte.,
Por exemplo, para dividir uma frase, as conjunções “e” e “ou”, expanda a Divisão por seqüências de grupo e introduza o delimitador de cadeias de caracteres por linha:
Como resultado, a fonte frase é separado em cada ocorrência de cada um delimitador:
e aqui outro exemplo da vida real., Supondo que você”ve importados de uma coluna de datas a partir de uma fonte externa, que o aspecto da seguinte forma:
5.1.2016 12:20
5.2.2016 14:50
Este formato não convencional para o Excel, e, portanto, nenhuma das funções de Data reconheceria qualquer data ou hora elementos. Para dividir o Dia, Mês, Ano, Horas e minutos em células separadas, indique os seguintes caracteres no campo “Split by strings”:
- Dot (.,) para separar o dia, mês e ano
- dois-pontos (:) para separar as horas dos minutos
- Espaço para separar data e hora
Bater o botão de Divisão, e você vai ver imediatamente o resultado:
Dividir células por máscara (padrão)
Separando uma célula a máscara significa dividir uma seqüência de caracteres com base em um padrão.
Esta opção dá muito jeito quando você precisa dividir uma lista de cadeias de caracteres homogêneas em alguns elementos, ou substratos., A complicação é que o texto fonte não pode ser dividido em cada ocorrência de um determinado delimitador, apenas em alguma ocorrência específica. O exemplo seguinte tornará as coisas mais fáceis de entender.
Supondo que você tem uma lista de cadeias de caracteres extraídos de algum arquivo de log:
o Que você quer é ter data e hora, se houver, o código de erro e exceção detalhes em 3 colunas separadas., Você não pode utilizar um espaço como delimitador porque existem espaços entre a data e a hora, que devem aparecer em uma coluna, e há espaços dentro do texto de exceção, que também devem aparecer em uma coluna.
a solução está dividindo uma cadeia pela seguinte máscara:*erro:*exceção: *
Onde o asterisco ( * ) representa qualquer número de caracteres.
os pontos (:) estão incluídos nos delimitadores porque não queremos que eles apareçam nas células resultantes.,
E agora, expanda a Divisão por máscara de seção na Divisão painel de Texto, digite a máscara em Enter delimitadores de caixa, e clique em Dividir:
O resultado será semelhante a este:
uma grande vantagem deste método é a flexibilidade.,você quer que eles aparecem em colunas diferentes, usar essa máscara:
* *ERRO:*Exceção:*
Traduzido para o inglês, a máscara instrui o add-in para dividir as seqüências originais em 4 partes:
- Todos os caracteres antes do 1º espaço encontrado dentro da cadeia (data)
- Caracteres entre o 1º espaço e a palavra ERRO: (o tempo)
- Texto entre ERRO: Excepção: (código de erro)
- Tudo o que vem depois de Exceção: (exceção de texto)
espero que gostem forma rápida e direta para dividir seqüências de caracteres no Excel., Se você está curioso para dar-lhe uma tentativa, uma versão de avaliação está disponível para download abaixo. Agradeço-lhe por ler e espero vê-lo em nosso blog na próxima semana!
downloads disponíveis
Excel Split Cells formulas (.xlsx file)
Ultimate Suite 14-day fully-functional version (.,arquivo zip)
- Como dividir células no Excel
- Como separar nomes no Excel: dividir o primeiro e último nome em diferentes colunas
- Como dividir data e hora no Excel
- Como mesclar duas colunas no Excel sem perda de dados