= 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çãoVisã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