Como extrair apenas os números de uma cadeia de texto no Excel?
Ao trabalhar com o Excel, é comum deparar-se com células onde números e texto estão misturados — como, por exemplo, «Encomenda 1234» ou «A pontuação é 95,6». Extrair apenas os valores numéricos dessas cadeias de texto é muitas vezes essencial para análise posterior, cálculos ou relatórios. Consoante a posição do número — no início, no fim ou incorporado algures no meio do texto — ou se pretende apenas números decimais, o Excel disponibiliza várias abordagens práticas para realizar esta tarefa com eficácia.
Neste guia, exploramos diversos métodos para extrair números de cadeias de texto no Excel, abrangendo cenários distintos — como extrair números independentemente da sua posição, lidar com números decimais e recuperar valores especificamente do início ou do fim de uma cadeia. Cada método inclui uma explicação clara do seu funcionamento, a respetiva usabilidade e dicas práticas para uma aplicação eficaz.
Extrair número de qualquer posição de uma cadeia de texto
Extrair apenas números decimais de uma cadeia de texto
Extrair número do fim da cadeia de texto
Extrair número do início da cadeia de texto
Extrair número de qualquer posição de uma cadeia de texto
Extrair números incorporados em qualquer posição de uma cadeia de texto é uma necessidade frequente, especialmente ao limpar dados importados de diversas fontes ou ao consolidar informações provenientes de múltiplas folhas. Existem várias soluções práticas e eficientes para este cenário, cada uma adaptada ao seu caso de uso ideal, consoante a versão do Excel que utiliza e o seu nível de familiaridade com fórmulas ou complementos.
♦ Extrair número de qualquer posição de uma cadeia de texto com o Flash Fill
O Flash Fill é uma funcionalidade rápida e intuitiva disponível no Excel 2013 e versões posteriores, que extrai números de uma cadeia de texto com base num padrão que você indica. Sem necessidade de fórmulas ou macros, é a solução ideal para cenários simples:
1. Numa célula adjacente à sua cadeia de texto, digite manualmente o número exatamente como pretende extraí-lo (por exemplo, se A2 contiver «Encomenda123A», introduza «123» em B2).
2. Com a célula ativa abaixo da sua entrada manual, prima Ctrl + E(ou utilize o separador)Dados > Flash Fill). O Excel preencherá automaticamente as células abaixo, extraindo números de células semelhantes com base no seu exemplo inicial.
Este método é excelente para conjuntos de dados pequenos a médios e ocasionais, desde que a sua estrutura seja consistente. Tenha em atenção que o Flash Fill depende do reconhecimento de padrões — se os seus dados forem muito inconsistentes ou contiverem uma mistura irregular de texto e números, poderá não obter os resultados pretendidos. Caso o Flash Fill não seja detetado automaticamente, verifique se está ativado em Ficheiro > Opções > Avançadas > Preenchimento Automático com Flash Fill.
♦ Extrair número de qualquer posição de uma cadeia de texto com fórmulas
Utilizar fórmulas é uma forma versátil de extrair números de cadeias de texto. Este método recorre a funções incorporadas ou a funções de matrizes dinâmicas e revela-se especialmente útil se precisar de uma solução que se atualize automaticamente sempre que os dados de origem forem alterados.
Selecione uma célula vazia onde deseja exibir o número extraído. Em seguida, insira uma das fórmulas abaixo, conforme a sua versão do Excel. Após introduzir a fórmula, arraste a alça de preenchimento para baixo para aplicá-la a outras células, conforme necessário. Consulte a ilustração abaixo como referência:
● Todas as versões do Excel:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")Esta fórmula é compatível com todas as versões modernas do Excel e constitui uma opção fiável caso não esteja a utilizar o Excel 365 ou o Excel 2021. Tenha em atenção que fórmulas com múltiplas funções podem impactar o desempenho em grandes conjuntos de dados. Certifique-se também de que as cadeias de texto incluem pelo menos um carácter numérico; caso contrário, a fórmula poderá devolver um resultado em branco ou um erro.
● Excel 365 ou Excel 2021 e versões posteriores:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Esta fórmula tira partido das novas funções de matrizes dinâmicas, simplificando significativamente o processo de extração para utilizadores do Office 365 ou do Excel 2021 e versões posteriores. Trata de forma eficaz tanto sequências alfanuméricas simples como as mais complexas. Certifique-se de que está a utilizar uma versão compatível; caso contrário, a fórmula poderá não funcionar como esperado.
Dica: ao utilizar fórmulas com números mistos, negativos ou com símbolos especiais, reveja sempre os resultados extraídos para garantir a sua precisão. Além disso, as fórmulas podem não reconhecer automaticamente os pontos decimais; por isso, se precisar de extrair números decimais, consulte a secção dedicada abaixo. Caso obtenha um resultado inesperado ou um erro, verifique novamente as referências das células e a sintaxe da fórmula.
♦ Extrair número de qualquer posição de uma cadeia de texto com Kutools para Excel
O Kutools para Excel oferece um método simples para extrair números de qualquer posição dentro de uma cadeia de texto. Especialmente indicado para utilizadores que preferem uma interface interativa em vez de fórmulas ou que precisam de processar grandes intervalos sem esforço manual, este método poupa tempo e simplifica tarefas, eliminando a necessidade de memorizar ou resolver problemas com fórmulas.
- Clique em Kutools > Texto > Extrair Texto, conforme ilustrado abaixo.
- Na caixa de diálogo Extrair Texto:
(1.) Selecione a lista de células que contêm as suas cadeias de texto;
(2.) Escolha a opção Extrair Números, que garante que apenas as partes numéricas sejam capturadas;
(3.) Se pretender que os números extraídos sejam atualizados automaticamente sempre que os Dados de Origem forem alterados, marque Inserir como Fórmula (caso contrário, os resultados serão texto estático);
(4.) Clique em OK.
- Quando solicitado, selecione uma célula para a saída. Escolha uma área vazia com espaço suficiente para os resultados e evite substituir acidentalmente dados existentes.
- Clique em OK. Os números de cada célula selecionada serão extraídos instantaneamente.
Esta abordagem é intuitiva e reduz significativamente o risco de erros em fórmulas ou referências incorretas de células. Contudo, tenha em atenção que o Kutools é um complemento externo e não está incluído na instalação padrão do Excel. É especialmente indicado para operações em lote e para utilizadores que pretendem contornar sintaxes complexas de funções ou o uso de VBA.
Se os números não forem extraídos conforme o esperado, verifique se o seu texto original contém formatação mista — como caracteres ocultos ou algarismos não convencionais. Além disso, mantenha sempre uma cópia de segurança da sua folha de cálculo antes de realizar alterações em massa.
♦ Extrair número de qualquer posição de uma cadeia de texto com código VBA
Para utilizadores mais avançados ou em situações específicas onde as fórmulas e os complementos não bastam, o VBA oferece uma abordagem flexível para extrair números de cadeias de texto no Excel. Altamente personalizável, este método revela-se especialmente útil para automatizar tarefas repetitivas ou processar conjuntos de dados não convencionais.
- Abra o editor do VBA premindo Alt + F11. Na janela do Microsoft Visual Basic for Applications, clique em Inserir > Módulo. Na nova janela do módulo, cole o seguinte código:
- Código VBA: extrair apenas números de uma cadeia de texto:
Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub - Após inserir o código, prima F5 ou clique no botão Executar. Irá aparecer uma caixa de diálogo a solicitar que selecione o intervalo de cadeias de texto a processar.
- Depois de selecionar o seu intervalo, clique em OK. Uma segunda caixa de diálogo pedir-lhe-á que indique a célula inicial para a saída.
- Clique novamente em OK. Todos os números incorporados na seleção original serão extraídos e colocados na área de saída que especificou.
As vantagens do método VBA incluem o tratamento eficiente de grandes conjuntos de dados e a possibilidade de personalizar o comportamento — como extrair apenas o primeiro número, lidar com valores decimais ou excluir símbolos específicos — desde que possua conhecimentos básicos de VBA. Precauções: as macros VBA não podem ser desfeitas e podem substituir dados caso a Área de Colocação da Lista escolhida se sobreponha aos seus dados originais; por isso, teste sempre primeiro num ficheiro de cópia de segurança. Certifique-se de que as macros estão ativadas no Excel.
Extrair apenas o número decimal de uma cadeia de texto
Por vezes, as suas cadeias de texto podem conter números com casas decimais (por exemplo, «peso:15,25kg»). Se precisar de extrair especificamente números decimais — incluindo a vírgula decimal —, as fórmulas padrão poderão não captar corretamente esses valores. As soluções abaixo foram concebidas precisamente para lidar com estas situações:
As fórmulas seguintes permitem extrair números decimais de cadeias de texto de forma eficiente. Introduza a fórmula adequada numa célula vazia junto aos seus dados e, em seguida, utilize a alça de preenchimento para a copiar para as linhas adicionais, conforme necessário.
● Todas as versões do Excel:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))Esta fórmula é compatível com a maioria das versões do Excel e foi especialmente concebida para reconhecer vírgulas decimais como parte integrante do número durante a extração. Verifique atentamente os seus dados para garantir que não existem pontos desnecessários no texto. Caso os valores decimais não sejam extraídos conforme esperado, reveja a cadeia original em busca de caracteres não padronizados ou problemas de formatação.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")Com o Excel 365, os arrays dinâmicos processam e devolvem de forma mais inteligente os números decimais extraídos de cadeias de texto complexas. Introduza a fórmula na célula de destino, prima Enter e, se necessário, estenda a fórmula para baixo.
Dica: se obtiver repetidamente zeros ou erros em vez de números, verifique se os seus dados contêm números decimais válidos e se as referências das células estão corretas. Caso os seus números incluam valores negativos e decimais, poderá precisar de ajustar as fórmulas.
Extrair número do fim da cadeia de texto
Quando os números surgem sempre no final das suas cadeias de texto (por exemplo, «221_ProductID_446» ou «923Ticket#2021»), poderá querer extrair apenas o número final («446» ou «2021»). A fórmula apresentada aqui foi concebida precisamente para esse cenário, assegurando que só as sequências numéricas situadas no final do texto são recuperadas.
Copie e cole a seguinte fórmula numa célula vazia. Nas versões mais antigas do Excel, confirme a fórmula com Ctrl + Shift + Enter para criar uma fórmula de matriz. Nas versões mais recentes do Excel com arrays dinâmicos, basta premir Enter:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))Arraste a alça de preenchimento para baixo para aplicar esta fórmula às restantes linhas. Este método é ideal para formatos consistentes em que só é necessário o último número de cada cadeia, ignorando quaisquer números que surjam no início ou no meio.
Nota: se os seus dados contiverem espaços, símbolos especiais ou variações na posição dos números, ajuste a fórmula ou utilize um método mais genérico, conforme descrito acima. Verifique sempre se o resultado obtido corresponde ao esperado, especialmente quando os seus dados incluírem texto que termina com caracteres não numéricos semelhantes a números.
Extrair número do início da cadeia de texto
Se precisar de extrair apenas os números que aparecem no início das suas cadeias de texto, uma fórmula dedicada permite-lhe isolar exclusivamente essa parte.
Coloque a fórmula numa célula de destino vazia e, nas versões mais antigas do Excel, utilize Ctrl + Shift + Enterpara criar uma fórmula de matriz (no Excel 365/2021, basta premir)Enter):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)Em seguida, arraste a fórmula para baixo por todas as linhas que necessitam de extração. Esta abordagem devolve apenas os números presentes no início da cadeia — mesmo que existam outros algarismos noutras posições, só a parte numérica inicial será capturada.
Dica: se o primeiro caractere da sua cadeia não for numérico, esta fórmula poderá devolver uma célula vazia ou um erro. Verifique e limpe os seus dados originais, conforme necessário, para obter os melhores resultados.
A extração de números de cadeias de texto é uma tarefa comum e frequentemente essencial na preparação de dados no Excel. O método mais adequado depende da estrutura dos seus dados, da versão do Excel e do seu à-vontade com fórmulas ou complementos. Se o seu cenário não se enquadrar nas soluções acima, considere combinar métodos ou explorar complementos especializados para necessidades avançadas. Verificar os resultados extraídos em busca de inconsistências, manter cópias de segurança e começar com amostras menores de dados pode ajudá-lo a evitar problemas comuns.
Artigos relacionados:
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos