Fórmula genérica
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Resumo
Para dividir um número de um valor de unidade, você pode usar uma fórmula baseada em várias funções: MAX, ISNUMBER, VALUE e MID. No exemplo mostrado, a fórmula em C5 é:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Observação: esta é uma fórmula experimental que usa uma constante de matriz codificada, definida aqui para referência e comentários. Testado apenas casualmente, então tome cuidado ao usar ou adaptar.
Explicação
Às vezes, você encontra dados que misturam unidades diretamente com números (ou seja, 8 km, 12v, 7,5 horas). Infelizmente, o Excel tratará os números neste formato como texto, e você não poderá realizar operações matemáticas em tais valores.
Para separar um número de um valor unitário, você precisa determinar a posição do último número. Se você adicionar 1 a essa posição, terá o início do texto da unidade. Essa fórmula usa esse conceito para descobrir onde começa a unidade de medida.
No exemplo mostrado, a fórmula em C5 é:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Esta fórmula usa a função MID para extrair os primeiros 9 valores em B5, um caractere por vez. O resultado é uma matriz como esta:
("8","0","v","","","","","","")
Em seguida, usamos a função VALUE para converter números em formato de texto em números reais. O resultado é:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Executamos esta matriz por meio de ISNUMBER para obter:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Em seguida, multiplique isso por outra matriz com 9 números para obter:
(1,2,0,0,0,0,0,0,0)
Então usamos MAX para obter o maior valor, que é a posição do "último número".
Finalmente, adicionamos 1 à posição para obter a posição de "início da unidade".
Finalmente, usamos esta posição com as funções ESQUERDA e DIREITA padrão para separar os números das unidades:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Observe que a constante de matriz de número codificada é um hack por conveniência e só manipulará valores brutos de até 9 caracteres de comprimento.
