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.
Ideias Chave
1. Remover espaços vazios.
2. Números que são texto alterados para numérico.
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.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).
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](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJuZGlqN4pGc-yDuDlzYcMpgH4dahxeZ_KXApEk3ay7Ar72sbT72pnU-Uhm04lhX3mB0yXOlX7u2IerJ5_DAsjwc9zwoFC4OjDoemIEdGfcGMtuyzlFAgMemiaXoJzFRG2G4WiLfblQFg/s16000-rw/ezgif.com-gif-maker+%25281%2529.gif)
Nota: No nosso caso, o Excel usa vírgulas como separador de casas decimais.
A solução que propomos
=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