Como dividir números e texto de uma string no Excel

Muitas vezes obtenho dados mistos de campo e servidor para análise. Esses dados geralmente estão sujos, tendo coluna misturada com número e texto. Ao fazer a limpeza dos dados antes da análise, separo os números e o texto em colunas separadas. Neste artigo, direi como você pode fazer isso.

Cenário:
Então, um nosso amigo no Exceltip.com fez esta pergunta na seção de comentários. “Como faço para separar os números que vêm antes e no final do texto usando Excel Formula. Por exemplo 125EvenueStreet e LoveYou3000 etc. ”

Para extrair texto, usamos RIGHT, LEFT, MID e outras funções de texto. Precisamos apenas saber o número de textos a extrair. E aqui faremos o mesmo primeiro.
Extraia o número e o texto de uma string quando o número estiver no final da string
Para o exemplo acima, preparei esta folha. Na célula A2, tenho o barbante. Na célula B2, quero a parte do texto e na C2 a parte do número.

Portanto, só precisamos saber a posição de onde começa o número. Em seguida, usaremos a esquerda e outra função. Portanto, para obter a posição do primeiro número, usamos a fórmula genérica abaixo:
Fórmula genérica para obter a posição do primeiro número na string:

= MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Isso retornará a posição do primeiro número.
Para o exemplo acima, escreva esta fórmula em qualquer célula.

= MIN (PROCURAR ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))

Extrair parte do texto

Ele retornará 15, pois o primeiro número encontrado está na 15ª posição no Texto. Eu explicarei isso mais tarde.

Agora, para obter o Texto, da esquerda precisamos apenas obter 15-1 caracteres da string. Então, vamos usar
Função ESQUERDA para extrair texto.
Fórmula para extrair texto da esquerda

= LEFT (A5, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")) - 1)

Aqui, apenas subtraímos 1 de qualquer número retornado por MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Extrair parte do número

Agora, para obter os números, precisamos apenas obter os caracteres numéricos do primeiro número encontrado. Então, calculamos o comprimento total de fragmento e subtrair a posição do primeiro número encontrado e adicionar 1 para isso. Simples. Sim, é apenas complexo, é simples.
Fórmula para extrair números da direita

= DIREITO (A5, LEN (A5) -MIN (PROCURAR ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")) + 1)

Aqui nós apenas obtemos o comprimento total da string usando a função LEN e então subtraímos a posição do primeiro número encontrado e então adicionamos 1 a ele. Isso nos dá o número total de números. Saiba mais aqui sobre como extrair texto usando as funções ESQUERDA e DIREITA do Excel.

Portanto, as funções ESQUERDA e DIREITA são simples. A parte complicada é MIN e a parte SEARCH que nos dá a posição do primeiro número encontrado. Vamos entender isso.
Como funciona
Nós sabemos como funcionam as funções ESQUERDA e DIREITA. Exploraremos a parte principal desta fórmula que obtém a posição do primeiro número encontrado e que é: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
A função SEARCH retorna a posição de um texto na string. A função SEARCH ('texto', 'string') leva dois argumentos, primeiro o texto que você deseja pesquisar e, em seguida, a string na qual deseja pesquisar.

• • Aqui em SEARCH, na posição do texto, temos uma matriz de números de 0 a 9. E na posição da string temos uma string que é concatenada com "0123456789" usando & operador. Porque? Eu vou te dizer.
  • Cada elemento na matriz {0,1,2,3,4,5,6,7,8,9} será pesquisado em determinada string e retornará sua posição na string de formato de matriz no mesmo índice da matriz.
  • Se algum valor não for encontrado, ocorrerá um erro. Portanto, todas as fórmulas resultarão em erro. Para evitar isso, concatenamos os números "0123456789" no texto. Para que encontre sempre cada número em string. Esses números estão no final, portanto, não causarão nenhum problema.
  • Agora, a função MIN retorna o menor valor do array retornado pela função SEARCH. Este menor valor será o primeiro número da string. Agora, usando esta função NUMBER e LEFT e RIGHT, podemos dividir o texto e as partes da string.

Vamos examinar nosso exemplo. Em A5, temos a string que contém o nome da rua e o número da casa. Precisamos separá-los em células diferentes.
Primeiro, vamos ver como conseguimos nossa posição de primeiro número na string.

• • MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): isso se traduzirá em MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Agora, como expliquei, a pesquisa irá pesquisar cada número na matriz {0,1,2,3,4,5,6,7,8,9} em Monta270123456789 e retornará sua posição em uma forma de array. A matriz retornada será {8,9,6,11,12,13,14,7,16,17}. Como?
0 será pesquisado na string. Ele é encontrado na posição 8. Portanto, nosso primeiro elemento é 8. Observe que nosso texto original tem apenas 7 caracteres. Pegue. 0 não faz parte de Monta27.
O próximo 1 será pesquisado na string e também não faz parte da string original, e obtemos sua posição 9.
Os próximos 2 serão pesquisados. Como é a parte da string original, obtemos seu índice como 6.
Da mesma forma, cada elemento é encontrado em alguma posição.

• • Agora, esta matriz é passada para a função MIN como MIN ({8,9,6,11,12,13,14,7,16,17}). MIN retorna o 6 que é a posição do primeiro número encontrado no texto original.
    E a história depois disso é bem simples. Usamos este número para extrair texto e números usando as funções ESQUERDA e DIREITA.

Extraia o número e o texto de uma string quando o número estiver no início da string
No exemplo acima, Number estava no final da string. Como extraímos o número e o texto quando o número está no início.

Eu preparei uma tabela semelhante à anterior. Ele apenas tem um número no início.

Aqui, usaremos uma técnica diferente. Contaremos o comprimento dos números (que é 2 aqui) e extrairemos esse número de caracteres à esquerda da String.
Portanto, o método é = LEFT (string, contagem de números)
Para contar o número de caracteres, esta é a fórmula.
Fórmula genérica para contar o número de números:

= SUM (LEN (string) -LEN (SUBSTITUTE (string, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Aqui,

• • • A função SUBSTITUTE substituirá cada número encontrado por “” (em branco). Se um número for encontrado como substituído e uma nova string for adicionada ao array, o string original de outra forma será adicionado ao array. Desta forma, teremos um array de 10 strings.
    • Agora, a função LEN retornará o comprimento dos caracteres em uma matriz dessas strings.
    • Então, do comprimento das strings originais, subtrairemos o comprimento de cada string retornada pela função SUBSTITUTE. Isso retornará novamente um array.
    • Agora, SUM adicionará todos esses números. Esta é a contagem de números em string.

Extrair parte do número da string

Agora, como sabemos o comprimento dos números em fragmento, vamos substituir esta função em LEFT.
Uma vez que temos nossa string um A11, nosso:

Fórmula para extrair números da ESQUERDA

= ESQUERDA (A11, SOMA (LEN (A11) -LEN (SUBSTITUIR (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))

Extrair parte do texto da string

Como sabemos o número de números, podemos subtraí-lo do comprimento total da string para obter os alfabetos numéricos na string e, em seguida, usar a função certa para extrair esse número de caracteres à direita da string.

Fórmula para extrair texto da DIREITA

= DIREITA (A11, LEN (A2) -SUM (LEN (A11) -LEN (SUBSTITUIR (A11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," "))))

Como funciona
A parte principal em ambas as fórmulas é SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) que calcula a primeira ocorrência de um número. Só depois de descobrir isso, podemos dividir o texto e o número usando a função ESQUERDA. Então, vamos entender isso.

• • • SUBSTITUTAR (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Esta parte retorna um array de string em A11 depois de substituir esses números por nada / espaço em branco (“”). Para 27Monta ele retornará {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
    • LEN (SUBSTITUIR (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Agora a parte SUBSTITUTE é envolvida pela função LEN. Este comprimento de retorno de textos na matriz retornado pela função SUBSTITUTE. Como resultado, teremos {7,7,6,7,7,7,7,6,7,7}.
    • LEN (A11) -LEN (SUBSTITUIR (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," ")): Aqui estamos subtraindo cada número retornado pela parte acima do comprimento da string real. O comprimento do texto original é 7. Portanto, teremos {7-7,7-7,7-6,….}. Finalmente teremos {0,0,1,0,0,0,0,1,0,0}.
    • SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Aqui usamos SUM para somar a matriz retornada pela parte acima da função. Isso dará 2. Que é o número de números em string.

Agora, usando isso, podemos extrair os textos e numerá-los e dividi-los em células diferentes. Este método funcionará com ambos os tipos de texto, quando o número está no início e quando está no final. Você só precisa utilizar as funções LEFT e RIGHT apropriadamente.
Use a função SplitNumText para dividir números e textos de uma string
Os métodos acima são um pouco complexos e não são úteis quando texto e números são misturados. Para dividir texto e números, use esta função definida pelo usuário.

Sintaxe:

= SplitNumText (string, op)

Fragmento: A string que você deseja dividir.
Op: isso é booleano. Passe 0 ou falso para obter a parte do texto. Para a parte numérica, passe verdade ou qualquer número maior que 0.
Por exemplo, se a string está em A20, então,
A fórmula para extrair números da string é:

= SplitNumText (A20,1)

E
A fórmula para extrair texto da string é:

= SplitNumText (A20,0)

Copie o código abaixo no módulo VBA para fazer a fórmula acima funcionar.

Função SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Then num = num & Mid (str, i , 1) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function 

Este código simplesmente verifica cada caractere na string, se é um número ou não. Se for um número, ele é armazenado na variável num e na variável txt. Se o usuário passar true para op, num é retornado, caso contrário, txt é retornado.

Esta é a melhor maneira de dividir o número e o texto de uma string, na minha opinião.
Você pode baixar a pasta de trabalho aqui, se desejar.

Então, sim pessoal, essas são as maneiras de dividir texto e números em células diferentes. Se tiver alguma dúvida ou melhor solução, entre em contato na seção de comentários abaixo. É sempre divertido interagir com os caras.

Clique no link abaixo para baixar o arquivo de trabalho:

Divida o número e o texto de uma célula

Artigos populares:
50 atalhos do Excel para aumentar sua produtividade
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel