Não há função interna no Excel para extrair números de uma string em uma célula (e vice-versa - remova a parte numérica e extraia a parte de texto de uma string alfanumérica).
No entanto, isso pode ser feito usando uma função do Excel ou algum código VBA simples.
Deixe-me primeiro dizer-lhe do que estou falando.
Suponha que você tenha um conjunto de dados como abaixo e queira extrair números de strings (como abaixo):
O método escolhido também depende da versão do Excel que você está usando:
- Para versões do Excel anteriores a 2016, você precisa usar uma fórmula um pouco mais longa
- Para o Excel 2016, você pode usar a função TEXTJOIN recém-introduzida
- Método VBA disponível para todas as versões do Excel
Conteúdo
Extraia números de strings no Excel (fórmulas do Excel 2016)
Essa fórmula só funciona no Excel 2016 porque usa a função TEXTJOIN recém-introduzida.
Além disso, essa fórmula pode extrair números no início, no final ou no meio de uma sequência de texto.
Observe que a fórmula TEXTJOIN descrita nesta seção reúne todos os caracteres numéricos.Por exemplo, se o texto for "10 ingressos custam US$ 200", o resultado será 10200.
Suponha que você tenha um conjunto de dados como o seguinte e queira extrair números de strings em cada célula:
A fórmula abaixo fornecerá a parte numérica de uma string no Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Esta é uma fórmula de matriz, então você precisa usar " Controle + Shift + Enter " em vez de usar Enter.
Esta fórmula retornará em branco (string vazia) se não houver números na string de texto.
Como funciona essa fórmula?
Deixe-me quebrar esta fórmula e tentar explicar como ela funciona:
- ROW(INDIRECT(“1:”&LEN(A2))) – Esta parte da fórmula fornecerá uma série de números a partir de um.A função LEN na fórmula retorna o número total de caracteres na string.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) – Esta parte da fórmula retornará uma matriz de #VALUEs!Erros ou números baseados em string.Todos os caracteres de texto na string se tornam #VALUE!Erros e todos os valores permanecem como estão.Isso acontece quando multiplicamos a função MID por 1.
- SEERRO((MID(A2,ROW(INDIRETO(“1:”&LEN(A2))),1)*1),””) – Ao usar a função SEERRO, todos os #VALUEs são removidos!Erro, apenas os números permanecerão.A saída desta parte se parece com isso - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – A função TEXTJOIN agora simplesmente combina caracteres de string Ainda existe (somente números) e ignora strings vazias.
Dica profissional:Se você quiser verificar a saída de uma fórmula parcial, selecione a célula, pressione F2 para entrar no modo de edição, selecione a parte da fórmula que deseja gerar e pressione F9.Você verá os resultados imediatamente.Então lembre-se de pressionar Control + Z ou pressione Escape.Não pressione enter.
Você também pode usar a mesma lógica para extrair partes de texto de strings alfanuméricas.Aqui está a fórmula para obter a parte do texto da string:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Uma pequena mudança nessa fórmula é que a função SE é usada para verificar se há erros na matriz que obtemos da função MID.Se for um erro, mantenha o valor, caso contrário, substitua-o por branco.
Em seguida, use TEXTJOIN para combinar todos os caracteres de texto.
注意: Embora esta fórmula seja válida, ela usa uma função volátil (a função INDIRETA).Isso significa que, se você o estiver usando com um grande conjunto de dados, poderá levar algum tempo para obter resultados.É melhor criar um backup antes de usar esta fórmula no Excel.
Extraia números de strings no Excel (para Excel 2013/2010/2007)
Se você tiver o Excel 2013. 2010. ou 2007, não poderá usar fórmulas TEXTJOIN, portanto, será necessário usar fórmulas complexas para fazer isso.
Suponha que você tenha um conjunto de dados como abaixo e queira extrair todos os números em strings em cada célula.
A fórmula abaixo fará isso:
=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 retornará em branco (string vazia) se não houver números na string de texto.
Embora esta seja uma fórmula de matriz, vocêSem necessidadeUse "Control-Shift-Enter" para usá-lo.Uma entrada simples funciona para esta fórmula.
O crédito por esta fórmula vai para o incrívelFórum do Sr. Excel.
Novamente, essa fórmula extrairá todos os números da string, independentemente da posição.Por exemplo, se o texto for "10 ingressos custam US$ 200", o resultado será 10200.
注意: Embora esta fórmula seja válida, ela usa uma função volátil (a função INDIRETA).Isso significa que, se você o estiver usando com um grande conjunto de dados, poderá levar algum tempo para obter resultados.É melhor criar um backup antes de usar esta fórmula no Excel.
Separe texto e números no Excel com VBA
Se você precisar separar texto e números com frequência (ou extrair números do texto), também poderá usar o método VBA.
Tudo o que você precisa fazer é criar uma função personalizada definida pelo usuário (UDF) no Excel usando código VBA simples e, em seguida, usar essa fórmula VBA em vez de uma fórmula longa e complexa.
Deixe-me mostrar como criar duas fórmulas em VBA - uma para extrair números e outra para extrair texto de strings.
Extraia números de strings no Excel (usando VBA)
Nesta parte, mostrarei como criar uma função personalizada para obter apenas a parte numérica de uma string.
Aqui está o código VBA que usaremos para criar esta função personalizada:
Função GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) Para i = 1 Para StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Função Final do Resultado
Aqui estão as etapas para criar essa função e usá-la em uma planilha:
Agora você poderá usar a função GetText na planilha.Como fizemos todo o trabalho pesado no próprio código, tudo o que você precisa fazer é usar a fórmula =GetNumeric(A2).
Isso fornecerá imediatamente a parte numérica da string.
Observe que, como a pasta de trabalho agora contém código VBA, você precisará salvá-la com uma extensão .xls ou .xlsm.
Se você precisar usar essa fórmula com frequência, também poderá salvá-la em sua pasta de trabalho de macro pessoal.Isso permitirá que você use essa fórmula personalizada em qualquer pasta de trabalho do Excel que você usar.
Extrair texto de string no Excel (usando VBA)
Nesta parte, mostrarei como criar uma função personalizada para obter apenas a parte de texto de uma string.
Aqui está o código VBA que usaremos para criar esta função personalizada:
Função GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) Para i = 1 Para StringLength Se Não (IsNumeric(Mid(CellRef, i, 1))) Então Result = Result & Mid(CellRef, i, 1 ) Em seguida, GetText = Result End Function
Aqui estão as etapas para criar essa função e usá-la em uma planilha:
Agora você poderá usar a função GetNumeric na planilha.Como fizemos todo o trabalho pesado no próprio código, tudo o que você precisa fazer é usar a fórmula =GetText(A2).
Isso fornecerá imediatamente a parte numérica da string.
Observe que, como a pasta de trabalho agora contém código VBA, você precisará salvá-la com uma extensão .xls ou .xlsm.
Se você precisar usar essa fórmula com frequência, também poderá salvá-la em sua pasta de trabalho de macro pessoal.Isso permitirá que você use essa fórmula personalizada em qualquer pasta de trabalho do Excel que você usar.
