Como extrair um número de uma string no Excel

Como

PorAdam Davidson

Publicados7 de junho de 2023

O Excel é uma planilha poderosa, mas não faz milagres. Se você estiver importando dados e esses dados estiverem uma bagunça, o Excel não poderá fazer muito com eles. Por exemplo, se você tiver valores anexados a outros caracteres como parte de uma string, não poderá realizar cálculos neles, a menos que os extraia primeiro.

Felizmente, é possível fazer exatamente isso, embora possa ser mais complicado do que você imagina. Veja como extrair um número de uma string no Excel.

Como extrair um número do final de uma string usando o Flash Fill

Uma maneira rápida e fácil de extrair um número do final de uma string é usar o Excel Preenchimento Flash recurso. Esse recurso permite preencher rapidamente um intervalo de células com base no padrão detectado em uma célula específica. Uma desvantagem desse método é que ele exige que você mesmo faça a primeira extração e, se quiser extrair apenas um número de uma única string, não será útil.

Na maioria dos casos, o Flash Fill pode extrair corretamente o número do final de uma string, embora não seja infalível, portanto, você deve sempre verificar os resultados com cuidado. Se não funcionar corretamente, tente preencher manualmente as primeiras duas ou três células em vez de apenas a primeira.

Para extrair um número do final de uma string usando o Flash Fill:

1. Selecione a célula ao lado da primeira célula que contém suas strings.
   
2. Digite o número que aparece no final da string.
   
3. Se todos os seus dados estiverem em uma coluna contínua sem lacunas, selecione a célula diretamente abaixo daquela em que você acabou de inserir o número.
   
4. Se seus dados tiverem lacunas em algumas linhas, selecione a primeira célula e todas as células abaixo das quais deseja extrair números, incluindo as linhas em branco.
   
5. Imprensa Ctrl+E no Windows ou Cmd+E no Mac para executar o recurso Flash Fill.
6. As células restantes serão preenchidas automaticamente com base no padrão detectado pelo Excel. Na maioria dos casos, os números do final da string serão extraídos corretamente.
   

Como extrair um número do final de uma string usando uma fórmula

Uma desvantagem de usar o método Flash Fill é que ele requer que você mesmo extraia números da string para a primeira linha. É possível extrair um número do final de uma string usando uma fórmula, embora a fórmula seja bastante complexa.

Para extrair um número do final de uma string usando uma fórmula:

1. Clique na célula onde deseja que o número extraído apareça.
2. Digite a seguinte fórmula, substituindo ‘B2’ pela célula que contém sua string:

   =VALOR(DIREITA(B2,LEN(B2)-MAX(SE(ÉNÚMERO(MEIO(B2,LINHA(INDIRETO("1:"&LEN(B2))),1)*1)=FALSO, LINHA(INDIRETO(" 1:"&LEN(B2))),0))))

3. Imprensa Digitar.
4. O número deve ser extraído da string.
5. Para aplicar a fórmula a quaisquer células restantes, clique na alça de arrastar no canto inferior direito da célula.
   
6. Arraste para baixo sobre as células onde deseja que a fórmula seja replicada.
   
7. Solte o mouse e a fórmula será copiada para as células selecionadas.
8. Essas células agora devem conter seus números extraídos.
   
9. Quaisquer células sem uma string para extrair retornarão um erro. Se você quiser retornar uma célula vazia, poderá usar a fórmula:

   =SEERRO(VALOR(DIREITA(B2,LEN(B2)-MAX(SE(ÉNÚMERO(MEIO(B2,LINHA(INDIRETO("1:"&LEN(B2))),1)*1)=FALSO, LINHA(INDIRETO ("1:"&LEN(B2))),0)))),"")

10. Quaisquer células vazias agora retornarão um espaço em branco.
    

Você pode estar se perguntando o que essa fórmula faz. Resumindo, ele gera um array com o mesmo comprimento da string. Ele coloca cada um dos caracteres da string neste array em ordem. Em seguida, avalia cada um desses caracteres para ver se eles são um número ou não. Quaisquer caracteres que sejam números são convertidos em zero. Quaisquer caracteres que não sejam números são convertidos em um número que representa sua posição na string.

Por exemplo, AB5HG23 seria convertido em 1, 2, 0, 4, 5, 0, 0. Em seguida, ele procura o valor mais alto nessa lista, que é a posição do último caractere que não é um número. Por fim, ele retorna todos os caracteres após esse ponto, que são os números no final da string. A função VALUE então converte isso de volta em um número. Simples, certo?

Como extrair um número do início de uma string usando o Flash Fill

Você também pode usar o Flash Fill para extrair um número do início de uma string, preenchendo você mesmo o primeiro exemplo e permitindo que o Excel interrompa o padrão.

Para extrair um número do início de uma string usando o Flash Fill:

1. Selecione a célula à direita da primeira string que contém um número que você deseja extrair.
2. Digite o número que aparece no início da string.
   
3. Se todos os seus dados estiverem em uma coluna contínua sem lacunas, selecione a célula logo abaixo.
   
4. Se seus dados tiverem lacunas, selecione a primeira célula e todas as células abaixo das quais você deseja extrair números, incluindo quaisquer linhas em branco.
   
5. Imprensa Ctrl+E no Windows ou Cmd+E no Mac para iniciar o Flash Fill.
6. As células selecionadas agora devem mostrar os números extraídos.
   

Como extrair um número do início de uma string usando uma fórmula

Você também pode usar uma fórmula para extrair um número do início de uma string. Isso funciona de maneira semelhante à fórmula da seção anterior, mas localiza a posição do primeiro caractere não numérico e retorna todos os caracteres anteriores a ele.

Para extrair um número do início de uma string usando uma fórmula:

1. Selecione a célula onde deseja que o número extraído apareça.
2. Digite a seguinte fórmula, substituindo ‘B18’ pela célula que contém sua string:

   =VALOR(ESQUERDA(B18,CORRESP(FALSO, ÉNÚMERO(MEIO(B18,LINHA(INDIRETO("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Imprensa Digitar e o número será extraído.
   
4. Você pode usar preenchimento automático no Excel para copiar a fórmula para qualquer outra célula necessária.
5. Se você quiser que qualquer linha vazia retorne um resultado vazio, use a seguinte fórmula:

   =SEERRO(VALOR(ESQUERDA(B18,CORRESP(FALSO, ÉNÚMERO(MEIO(B18,LINHA(INDIRETO("1:"&LEN(B18)+1)),1)*1),0)-1))" ")

Como extrair números de qualquer lugar em uma string usando o Flash Fill

Conforme mencionado anteriormente, o Flash Fill pode não funcionar se a primeira célula não corresponder ao formato de algumas das outras células. Nesse caso, pode ser necessário calcular manualmente duas ou três células antes que o Flash Fill forneça o resultado necessário.

Para extrair números de qualquer lugar em uma string usando o Flash Fill:

1. Na célula ao lado da primeira string, digite manualmente os números que estão na string.
   
2. Se todos os seus dados estiverem em uma coluna contínua, selecione a célula logo abaixo.
   
3. Se seus dados tiverem algumas lacunas, selecione a primeira célula e todas as células abaixo das quais você deseja extrair números, incluindo quaisquer linhas em branco.
   
4. Imprensa Ctrl+E no Windows ou Cmd+E no Mac para iniciar o Flash Fill.
5. As células agora devem ser preenchidas com os números extraídos.
   

Como extrair números de qualquer lugar em uma string usando uma fórmula

Você pode usar uma fórmula para extrair todos os números de uma string, não importa onde eles apareçam.

Para extrair números de qualquer lugar em uma string usando uma Fórmula:

1. Selecione a célula onde deseja que o número extraído apareça.
2. Digite a seguinte fórmula, substituindo ‘B9’ pela célula que contém sua string:

   =SOMAPRODUTO(MEIO(0&B9,LARGE(ÍNDICE(ÉNÚMERO(--MEIO(B9,LINHA(INDIRETO("1:"&LEN(B9))),1))*LINHA(INDIRETO("1:"&LEN(B9) )),0),LINHA(INDIRETO("1:"&LEN(B9))))+1,1)*10^LINHA(INDIRETO("1:"&LEN(B9)))/10)

3. Sua fórmula deve extrair todos os números da string.
   
4. Se você quiser que qualquer célula em branco retorne uma célula vazia, em vez de zero, use esta fórmula:

   =SEERRO(SOMAPRODUTO(MEIO(0&B9,LARGE(ÍNDICE(NÚMERO(--MEIO(B9,LINHA(INDIRETO("1:"&LEN(B9))),1))*LINHA(INDIRETO("1:"&LEN( B9))),0),LINHA(INDIRETO("1:"&LEN(B9))))+1,1)*10^LINHA(INDIRETO("1:"&LEN(B9)))/10)," ")

5. Você pode usar preenchimento automático para aplicar a fórmula a qualquer outra célula em sua planilha.
6. Quaisquer strings que não contenham nenhum número retornarão um zero. Você pode aprender como converter um zero em um traço no Excel se você quiser mudar isso.

Amplie suas habilidades no Excel

Aprender a extrair um número de uma string no Excel pode ajudá-lo a extrair rapidamente os dados necessários de uma lista de strings. Embora as fórmulas necessárias possam ser complexas, você não precisa entender exatamente como elas funcionam; só que eles fazem!

Existem truques de Excel mais úteis que você pode aprender para levar suas habilidades para o próximo nível. Você pode aprender como contar células com texto neles, ou como calcular a idade de alguém a partir da data de nascimento. Você também pode criar um gerador de números aleatórios em Excel.