Excel

Divida o texto e os números

Split Text Numbers

Fórmula do Excel: divida o texto e os númerosFórmula genérica
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))
Resumo

Para separar texto e números, você pode usar uma fórmula baseada no Função ENCONTRAR , a Função MIN , e as Função LEN com o DEIXOU ou DIREITO função, dependendo se você deseja extrair o texto ou o número. No exemplo mostrado, a fórmula em C5 é:





 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},B5&'0123456789'))

que retorna 7, a posição do número 3 na string 'apples30'.

Explicação

Visão geral

A fórmula parece complexa, mas a mecânica é na verdade bastante simples.





Como acontece com a maioria das fórmulas que dividem ou extraem texto, a chave é localizar o posição do que você está procurando. Depois de obter a posição, você pode usar outras funções para extrair o que precisa.

Nesse caso, estamos assumindo que os números e o texto são combinados e que o número aparece após o texto. A partir do texto original, que aparece em uma célula, você deseja dividir o texto e os números em células separadas, assim:



Original Texto Número
Maçãs 30 Maçãs 30
pêssegos24 pêssegos 24
laranjas 12 laranjas 12
pêssegos0 pêssegos 0

Conforme declarado acima, a chave neste caso é localizar a posição inicial do número, o que você pode fazer com uma fórmula como esta:

usando várias instruções if no Excel
 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

Assim que tiver a posição, para extrair apenas o texto, use:

 
= LEFT (A1,position-1)

E, para extrair apenas o número, use:

 
= RIGHT (A1, LEN (A1)-position+1)

Na primeira fórmula acima, estamos usando a função FIND para localizar a posição inicial do número. Para find_text, estamos usando a constante de array {0,1,2,3,4,5,6,7,8,9}, isso faz com que a função FIND execute uma pesquisa separada para cada valor na constante de array. Como a constante da matriz contém 10 números, o resultado será uma matriz com 10 valores. Por exemplo, se o texto original for 'maçãs30', a matriz resultante será:

 
{8,10,11,7,13,14,15,16,17,18}

Cada número neste array representa a posição de um item na constante do array dentro do texto original.

Em seguida, a função MIN retorna o menor valor da lista, que corresponde à posição na primeiro numero que aparece no texto original. Em essência, a função FIND obtém todas as posições numéricas, e MIN nos dá a primeira posição numérica: observe que 7 é o menor valor na matriz, que corresponde à posição do número 3 no texto original.

Você pode estar se perguntando sobre a estranha construção para dentro do texto na função find:

o que é um intervalo de células no excel
 
B5&'0123456789'

Esta parte da fórmula concatena todos os números possíveis de 0 a 9 com o texto original em B5. Infelizmente, FIND não retorna zero quando um valor não é encontrado, então esta é apenas uma maneira inteligente de evitar erros que podem ocorrer quando um número não é encontrado.

Neste exemplo, uma vez que estamos assumindo que o número sempre aparecerá segundo no texto original, funciona bem porque MIN força apenas o menor, ou primeiro ocorrência, de um número a ser retornado. Contanto que seja um número faz aparecer no texto original, essa posição será retornada.

Se o texto original não contiver nenhum número, uma posição 'falsa' igual ao comprimento do texto original + 1 será retornada. Com esta posição falsa, a fórmula ESQUERDA acima ainda retornará o texto e a fórmula DIREITA retornará um string vazia ('').

Autor Dave Bruns


^