Assista ao vídeo - Como extrair números de string de texto no Excel (usando fórmula e VBA)
Não há função embutida no Excel para extrair os números de uma string em uma célula (ou vice-versa - remova a parte numérica e extraia a parte do texto de uma string alfanumérica).
No entanto, isso pode ser feito usando um coquetel de funções do Excel ou algum código VBA simples.
Deixe-me primeiro mostrar do que estou falando.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja extrair os números da string (conforme mostrado abaixo):
O método escolhido também dependerá da versão do Excel que você está usando:
- Para versões anteriores ao Excel 2016, você precisa usar fórmulas um pouco mais longas
- Para Excel 2016, você pode usar a função TEXTJOIN recém-introduzida
- O método VBA pode ser usado em todas as versões do Excel
Clique aqui para baixar o arquivo de exemplo
Extrair Números de String no Excel (Fórmula para Excel 2016)
Esta fórmula funcionará apenas no Excel 2016, pois usa a função TEXTJOIN recém-introduzida.
Além disso, esta fórmula pode extrair os números que estão no início, no final ou no meio da sequência de texto.
Observe que a fórmula TEXTJOIN abordada nesta seção forneceria todos os caracteres numéricos juntos. Por exemplo, se o texto for “O preço de 10 ingressos é US $ 200”, o resultado será 10200.Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja extrair os números das strings em cada célula:
Abaixo está a fórmula que fornecerá a parte numérica de uma string no Excel.
= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRETO ("1:" & LEN (A2))), 1) * 1), ""))
Esta é uma fórmula de matriz, então você precisa usar 'Control + Shift + Enter‘Em vez de usar Enter.
Caso não haja números na string de texto, esta fórmula retornaria um espaço em branco (string vazia).
Como essa fórmula funciona?
Deixe-me quebrar esta fórmula e tentar explicar como funciona:
- LINHA (INDIRETO (“1:” & LEN (A2))) - esta parte da fórmula forneceria uma série de números começando em um. A função LEN na fórmula retorna o número total de caracteres na string. No caso de “O custo é de US $ 100”, ele retornará 19. As fórmulas se tornariam, portanto, ROW (INDIRETO (“1:19”). A função ROW retornará uma série de números - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, ROW (INDIRETO (“1:” & LEN (A2))), 1) * 1) - Esta parte da fórmula retornaria uma matriz de #VALOR! erros ou números com base na string. Todos os caracteres de texto na string tornam-se #VALUE! erros e todos os valores numéricos permanecem como estão. Isso acontece porque multiplicamos a função MID por 1.
- IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1) * 1), ””) - Quando a função IFERROR é usada, ela removeria todos os #VALUE! erros e apenas os números permaneceriam. A saída desta parte ficaria assim - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; 1; 0; 0}
- = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRETO (“1:” & LEN (A2))), 1) * 1), ””)) - A função TEXTJOIN agora simplesmente combina os caracteres da string que permanece (que são apenas os números) e ignora a string vazia.
Baixe o arquivo de exemplo
Você também pode usar a mesma lógica para extrair a parte do texto de uma string alfanumérica. Abaixo está a fórmula que obteria 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 nesta fórmula é que a função IF é usada para verificar se o array que obtemos da função MID contém erros ou não. Se for um erro, ele mantém o valor, senão o substitui por um espaço em branco.
Em seguida, TEXTJOIN é usado para combinar todos os caracteres de texto.
Cuidado: Embora esta fórmula funcione bem, ela usa uma função volátil (a função INDIRETA). Isso significa que, caso você use isso com um grande conjunto de dados, pode levar algum tempo para fornecer os resultados. É melhor criar um backup antes de usar esta fórmula no Excel.Extrair Números de String no Excel (para Excel 2013/2010/2007)
Se você tem o Excel 2013. 2010. ou 2007, você não pode usar a fórmula TEXTJOIN, então você terá que usar uma fórmula complicada para fazer isso.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja extrair todos os números da string 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 e A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2)))), 1)) * LINHA (INDIRETO ("$ 1: $" & LEN (A2))), 0), LINHA (INDIRETO ("$ 1: $" e LEN (A2)))) + 1,1) * 10 LINHA (INDIRETO ("$ 1: $" & LEN (A2))) / 10), "")
Caso não haja número na string de texto, esta fórmula retornaria em branco (string vazia).
Embora esta seja uma fórmula de matriz, você não precisa para usar 'Control-Shift-Enter' para usar isso. Uma simples entrada funciona para esta fórmula.
O crédito a esta fórmula vai para o incrível fó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 “O preço de 10 ingressos é US $ 200”, o resultado será 10200.
Cuidado: Embora esta fórmula funcione bem, ela usa uma função volátil (a função INDIRETA). Isso significa que, caso você use isso com um grande conjunto de dados, pode levar algum tempo para fornecer os resultados. É melhor criar um backup antes de usar esta fórmula no Excel.Separe texto e números no Excel usando VBA
Se separar texto e números (ou extrair números do texto) é algo que você precisa com freqüência, você também pode usar o método VBA.
Tudo o que você precisa fazer é usar um código VBA simples para criar uma função definida pelo usuário (UDF) personalizada no Excel e, em vez de usar fórmulas longas e complicadas, usar essa fórmula VBA.
Deixe-me mostrar como criar duas fórmulas em VBA - uma para extrair números e outra para extrair texto de uma string.
Extraia Números de String no Excel (usando VBA)
Nesta parte, vou mostrar como criar a função personalizada para obter apenas a parte numérica de uma string.
Abaixo 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) For i = 1 To 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 esta função e, em seguida, usá-la na planilha:
- Vá para a guia Desenvolvedor.
- Clique em Visual Basic (você também pode usar o atalho de teclado ALT + F11)
- No back-end do Editor VB que é aberto, clique com o botão direito em qualquer um dos objetos da pasta de trabalho.
- Vá para Inserir e clique em Módulo. Isso irá inserir o objeto de módulo para a pasta de trabalho.
- Na janela de código do módulo, copie e cole o código VBA mencionado acima.
- Feche o Editor VB.
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á instantaneamente apenas a parte numérica da string.
Observe que, como a pasta de trabalho agora contém o código VBA, você precisa salvá-la com a extensão .xls ou .xlsm.
Baixe o arquivo de exemplo
No caso de você ter que usar essa fórmula com frequência, você também pode salvá-la em sua Pasta de trabalho pessoal de macros. Isso permitirá que você use essa fórmula personalizada em qualquer uma das pastas de trabalho do Excel com as quais você trabalha.
Extraia texto de uma string no Excel (usando VBA)
Nesta parte, vou mostrar como criar a função personalizada para obter apenas a parte do texto de uma string.
Abaixo 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) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1) ) Next i GetText = Result End Function
Aqui estão as etapas para criar esta função e, em seguida, usá-la na planilha:
- Vá para a guia Desenvolvedor.
- Clique em Visual Basic (você também pode usar o atalho de teclado ALT + F11)
- No back-end do Editor VB que é aberto, clique com o botão direito em qualquer um dos objetos da pasta de trabalho.
- Vá para Inserir e clique em Módulo. Isso irá inserir o objeto de módulo para a pasta de trabalho.
- Se você já possui um módulo, clique duas vezes nele (não é necessário inserir um novo se já o tiver).
- Se você já possui um módulo, clique duas vezes nele (não é necessário inserir um novo se já o tiver).
- Na janela de código do módulo, copie e cole o código VBA mencionado acima.
- Feche o Editor VB.
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á instantaneamente apenas a parte numérica da string.
Observe que, como a pasta de trabalho agora contém o código VBA, você precisa salvá-la com a extensão .xls ou .xlsm.
Caso você precise usar essa fórmula com frequência, também pode salvá-la em sua Pasta de trabalho pessoal de macros. Isso permitirá que você use essa fórmula personalizada em qualquer uma das pastas de trabalho do Excel com as quais você trabalha.
Caso você esteja usando o Excel 2013 ou versões anteriores e não tenha
