Transformar com COMPACTAR e SUBST texto em número e limpar vazios

Como resolver dados não reconhecidos como números poluídos com espaços, virgulas e pontos.
João
Ideias Chave
1. Remover espaços vazios.
2. Números que são texto alterados para numérico.

Problema

Os dados não são reconhecidos como números e estão poluídos com espaços e têm várias virgulas e pontos.

Dados não normalizados

Identificamos várias dificuldades no quadro acima.

1. Espaços em branco antes e depois dos números
2. Números separados por . e ,

Para o Excel, este quadro é simplesmente texto. Tal como está não há qualquer informação que se possa retirar e/ou trabalhar com cálculos. Como resolver estes problemas para no fim termos números que o Excel possa trabalhar?

Identificar o tipo de espaço em branco

A primeira questão que se coloca é, será que estes espaços em branco são realmente espaços em branco?
No nosso exemplo, colocamos propositadamente um espaço em branco que não é exactamente um espaço. Este caracter, que aparentemente é um espaço em branco, é na verdade o código   (entende mais sobre ASCII) que surge de forma recorrente quando puxamos informação de fontes externas.

Para identificar o tipo de espaço podemos usar a Função CÓDIGO (Code).

Função code

Um espaço dado pela tecla espaço corresponde ao código 32. O caracter   que incluimos propositadamente neste exemplo corresponde ao código 160.

Virgulas ou pontos

Qual a configuração do Excel em que trabalhamos para as casas decimais? Ponto ou vírgula?
Configuração excel

Nota: No nosso caso, o Excel usa vírgulas como separador de casas decimais.

A solução que propomos

Solução compactar e subst
=COMPACTAR(SUBST(SUBST(SUBST(B$2;",";"");".";",");CARÁT(160);""))*1
em que:
A Função COMPACTAR (Trim) remove espaços do final e do início da célula. Neste exemplo, vamos usá-la em conjunto com a Função SUBST (Substitute) para definirmos os caracteres que queremos mudar.

=COMPACTAR(SUBST(SUBST(SUBST // vamos usar a Função SUBST três vezes dentro da Função COMPACTAR

(B$2;",";"") // substituir todas as vírgulas por nenhum caracter, ou seja apagar as vírgulas

;".";",") // substituir o ponto por uma vírgula (no nosso excel a vírgula é o separador decimal)

;CARÁT(160);"")) // substituir os espaços em branco que, tal como descobrimos no primeiro passo, corresponde ao caracter 160

*1> // multiplicamos por 1 transformando os campos imediatamente em números.

Descargas

Descarrega aqui o ficheiro com os exemplos para transformar texto em número e limpar vazios