Como extrair um número ou texto de excel com esta função

Video: EXCEL 2013 - Localizando PALAVRA(s) em campo de TEXTO - (SE, PROC, ÉNÚM, SOMA, LOCALIZAR, CONT.SE)

Excel é grande em trabalhar com ambos os números e texto-mas se você estiver usando ambos na mesma célula, você pode executar em alguma dificuldade. Felizmente, você pode extrair números ou texto a partir de células de trabalhar com os seus dados de forma mais eficiente. Nós demonstramos várias opções, dependendo do formato que os seus dados se encontra.Economia de tempo com operações de texto em ExcelEconomia de tempo com operações de texto em ExcelExcel pode fazer mágica com números e ele pode lidar com personagens igualmente bem. Este manual demonstra como analisar, converter, substituir e editar o texto dentro de planilhas. Estes princípios permitirá que você executar transformações complexas.consulte Mais informação

Os números armazenados como texto

Esta é uma situação comum, e, felizmente, muito fácil de lidar. Às vezes, as células que contêm apenas números são rotulados incorretamente como texto, impedindo Excel de usá-los em operações. Você pode ver na imagem abaixo que as células na coluna A são armazenados como texto, como indicado pela bandeira verde em cada célula, bem como a caixa de formato, sendo que ambos são destacadas a seguir.

Video: Curso de Excel Função Extrair Texto EXT.TEXTO() Dados de uma sequência de caracteres

Converter para número

Para resolver o problema, basta clicar em uma célula, clique na caixa de aviso, e selecione “Convert to número.” Isso é tudo que existe para ela! Isso pode ser feito em várias células, selecionando-os todos e clicando na caixa de aviso, mas isso provavelmente não é tão eficiente quanto o método seguinte.

Texto para Colunas

Se você tem um monte de células que precisam ser corrigidos, utilizando este método pode levar uma quantidade monumental de tempo. Para obter através do processo mais rápido, você pode usar o Excel de Texto para Colunas função. Selecione os números que você deseja converter, ir para Dados gt; Texto para Colunas, e usar o assistente para certificar-se os números saem corretamente (na maioria das vezes, você só precisa clicar Próximo e Terminar sem mexer com qualquer uma das configurações).Como converter texto delimitado arquivos para planilhas ExcelComo converter texto delimitado arquivos para planilhas ExcelUse Microsoft Excel para importar dados de um arquivo de texto em uma planilha. O Assistente de importação de texto ajuda a importar dados de texto delimitado e organizá-lo ordenadamente.consulte Mais informação

O texto será convertido em números e você está pronto para ir. Note que isto só funciona em uma coluna de cada vez, por isso, se você tem um monte de colunas, você pode querer ir com este método final.

Colar especial

Para que esse método funcione, você precisa digitar o número 1 em uma célula (é importante que este é em formato de número). Selecione essa célula e copiá-lo. Agora, selecione todos os números que você deseja converter para o formato número, e bateu Editar gt; Colar especial. Selecione “Multiply” na seção de Operação e clique em OK.

Todos os números que você selecionou será convertido para o formato geral, que reconhece números. Quaisquer células de texto também será convertido para o formato geral, o que não deve causar problemas.

Extrair números ou o texto a partir de células mistas-Definição

Agora chegamos à parte mais difícil: conseguir números para fora das células que contêm vários formatos de entrada. Se você tem um número e uma unidade (como “7 pás,” como temos abaixo), você vai executar para esse problema. Para resolvê-lo, vamos olhar para um par maneiras diferentes de dividir células em números e texto, permitindo-lhe trabalhar com cada um individualmente. O primeiro método é um pouco complicado, mas funciona muito bem em pequenos conjuntos de dados.

ESQUERDA DIREITA & PESQUISA

A principal função estaremos usando aqui é ESQUERDA, que retorna os caracteres mais à esquerda de uma célula. Como você pode ver no nosso conjunto de dados acima, temos células com um, dois, e os números de três caracteres, por isso vamos ter que devolver o mais à esquerda um, dois ou três caracteres das células. Ao combinar esquerdo com o função PESQUISA, podemos voltar tudo à esquerda do espaço. Aqui está a função:Encontrar nada em sua planilha Excel com funções de pesquisaEncontrar nada em sua planilha Excel com funções de pesquisaEm uma planilha gigante Excel, CTRL + F só vai chegar tão longe. Seja inteligente e permitem fórmulas fazer o trabalho duro. fórmulas de pesquisa economizar tempo e são fáceis de aplicar.consulte Mais informação

Video: #76 VBA - Excel 2016 - Como criar uma função para separar texto e números

= ESQUERDA (A1, DE BUSCA (" ", A1, 1))

Isto irá devolver tudo à esquerda do espaço. usando o preencher punho para aplicar a fórmula para o resto das células, isso é o que temos (você pode ver a fórmula na barra de função no topo da imagem):Como poupar tempo no Excel usando a alça de preenchimentoComo poupar tempo no Excel usando a alça de preenchimentoconsulte Mais informação

Como você pode ver, agora temos todos os números isolados, para que eles possam ser manipulados. Quer isolar o texto também? Podemos usar a função DIREITA da mesma forma:

= DIREITA (A1, LEN (A1) -search (" ", A1, 1))

Isto devolve x caracteres a partir do lado direito da célula, onde x é o comprimento total da célula menos o número de caracteres a esquerda do espaço.

Agora, o texto pode ser manipulado também. Quer combiná-los novamente? Basta usar a função CONCATENATE com todas as células como entradas:

= CONCATENAR (E1, F1)

Obviamente, este método funciona melhor se você tiver apenas números e unidades, e nada mais. Se você tiver outros formatos de célula, você pode ter que ser criativo com fórmulas para obter tudo funcionar direito. Se você tem um conjunto de dados gigante, vai valer a pena o tempo que leva para obter a fórmula descobri!

Texto para Colunas

O texto a função de colunas é útil para colunas números-somente, mas também pode tornar a vida mais fácil se você tem células de formato misturado. Selecione a coluna que deseja trabalhar com e bater Dados gt; Texto para Colunas. então você pode usar o assistente para selecionar um delimitador (um espaço é geralmente o melhor) e ter sua coluna de dividir o jeito que você quiser.

Se você só tem números um e dois dígitos, a opção Largura Fixa pode ser útil também, como ele só irá separou os dois ou três primeiros caracteres da célula (você pode criar uma série de splits, se quiser, mas eu vou guardar uma explicação completa sobre a divisão largura fixa para outro artigo).

Se o seu conjunto de dados contém uma grande quantidade de colunas, e você preferir não usar texto para colunas para cada um deles, você pode facilmente obter o mesmo efeito usando uma exportação rápida e importação. Primeiro, exporte sua planilha como um arquivo de valores separados por vírgulas (CSV). Clique Arquivo gt; Salvar como… e salve o arquivo como um arquivo CSV.

Agora, abra uma nova planilha e clique Arquivo gt; Importar… Selecione o arquivo CSV e usar o assistente de importação para obter a sua divisão de dados em duas colunas (você vai usar os mesmos passos como você fez com o texto para assistente Colunas). Ao selecionar “Space” na seção delimitadores, você vai dizer Excel para dividir os dados onde quer que encontra um espaço, que irá isolar os números e texto.

Clique em Concluir quando estiver pronto, e você terá uma nova planilha que tem suas colunas dividido em dois. Claro, se você tem mais de um espaço em uma célula, você vai acabar com mais de duas colunas, como você pode ver aqui:

Infelizmente, não é uma boa solução para este usando este metodolo- você vai ter para concatenar as células de volta juntos.

Situações mais complicadas

Com as estratégias acima, você deve ser capaz de extrair números ou texto fora da maioria das células de formato misto que estão dando-lhe o problema. Mesmo se não fizer isso, você provavelmente pode combiná-los com algumas das funções de texto poderoso incluídos no Excel para obter os caracteres que você está procurando. No entanto, existem algumas situações muito mais complicadas que exigem soluções mais complexas.

Por exemplo, eu encontrei um post no fórum onde alguém queria extrair os números de uma string como “45t *&65 /”, para que ele iria acabar com‘4565.’Outro cartaz deu a seguinte fórmula como uma maneira de fazê-lo:

= SUMPRODUCT (MID (0&A1, GRANDE (INDEX (ISNUMBER (- MID (A1, FILEIRA ($ 1: $ 25), 1)) * FILEIRA ($ 1: $ 25), 0), FILEIRA ($ 1: $ 25)) + 1,1) * 10 ^ ROW ($ 1: $ 25) / 10)

Para ser completamente honesto, eu não tenho nenhuma idéia de como ele funciona. Mas de acordo com o post no fórum, vai levar os números fora de uma seqüência complicada de números e outros caracteres. O ponto é que, com tempo suficiente, paciência e esforço, você pode extrair números e texto a partir de praticamente qualquer coisa! Você apenas tem que encontrar os recursos adequados.Precisa de Ajuda com fórmulas do Excel? 7 Recursos para ConsultarPrecisa de Ajuda com fórmulas do Excel? 7 Recursos para ConsultarExcel é o padrão planilha ouro. Se você é obrigado a usar o Excel e ainda têm de se familiarizar com ele, esses recursos rapidamente irá apresentá-lo para o básico e mais.consulte Mais informação

Que estratégia você usa?

Agora que você tem uma idéia melhor de como obter números e letras para fora das células de formato misto, queremos saber o que outros estratégias que você usou para fazer a mesma coisa. Publique o seu fórmulas, processos, ou qualquer outra coisa que possa ser útil nos comentários para que todos possamos aprender com eles!