Fó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 na função FIND, na função MIN e na função LEN com a função LEFT ou RIGHT, 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 é bastante simples.
Como acontece com a maioria das fórmulas que dividem ou extraem texto, a chave é localizar a 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:
=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 o find_text, estamos usando a constante de matriz (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 matriz. 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.
A seguir, a função MIN retorna o menor valor da lista, que corresponde à posição do primeiro número 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 construção estranha de within_text na função find:
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á em segundo lugar no texto original, ele funciona bem porque MIN força apenas o menor, ou primeira ocorrência, de um número a ser retornado. Enquanto um número 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á uma string vazia ("").
