Como utilizar o VLOOKUP com números armazenados como texto no Excel?
Ao utilizar o VLOOKUP no Excel, incompatibilidades de formato — como quando um valor de procura está armazenado como texto enquanto a coluna de procura contém números, ou vice-versa — podem causar falhas ou erros na pesquisa. Essa discrepância é um problema frequente, especialmente ao trabalhar com dados provenientes de fontes externas, importados ou em conjuntos de dados extensos e colaborativos. Resolver essas incompatibilidades é essencial para garantir que o VLOOKUP funcione conforme o esperado e devolva a informação correta. Este guia passo a passo apresenta soluções práticas para corrigir essas inconsistências de formatação, assegurando pesquisas fiáveis e precisas, independentemente da forma como os números estão armazenados no seu livro.
Este artigo apresenta formas eficazes de resolver esses erros, incluindo ajustes de fórmulas, ferramentas integradas do Excel e automatização com VBA para processamento em massa ou automatizado. Aborda ainda as vantagens e considerações de cada método, ajudando-o a escolher a abordagem mais adequada ao seu cenário.
- Procurar valores com VLOOKUP quando os números estão armazenados como texto, utilizando fórmulas
- Corrija rapidamente incompatibilidades de formato com Kutools para Excel
- Macro VBA: Normalizar formatos antes do VLOOKUP
- Outros métodos incorporados no Excel: Utilize «Texto em Colunas» para corrigir formatos de dados
Procurar valores com VLOOKUP quando os números estão armazenados como texto, utilizando fórmulas
Se os seus dados de pesquisa incluírem números armazenados como texto num local e números reais noutro, o VLOOKUP pode falhar ao encontrar correspondências devido a essa inconsistência de formato. Uma das soluções mais diretas consiste em utilizar fórmulas do Excel que convertam, em tempo real, o valor de pesquisa ou a coluna de pesquisa para um formato consistente. Esta abordagem funciona na maioria das operações em folhas de cálculo, é fácil de aplicar e mantém os seus dados originais inalterados.
Por exemplo, se o valor que está a procurar estiver armazenado como texto, enquanto o campo correspondente na tabela estiver formatado como número, pode utilizar a função VALOR para converter o texto em número diretamente na fórmula VLOOKUP.
Introduza a seguinte fórmula numa célula vazia onde pretender apresentar o resultado:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Após introduzir a fórmula, prima a tecla Enterpara obter o valor correspondente aos seus critérios, conforme ilustrado na imagem seguinte:
Explicação dos parâmetros e dicas:
- G1: A célula que contém o valor que pretende encontrar (pode ser texto ou número).
- A2:D15: O intervalo da sua tabela de dados que inclui a coluna de pesquisa e as colunas com as informações que pretende obter.
- 2: O número da coluna (contando a partir da coluna mais à esquerda do intervalo da tabela) que contém o resultado que pretende devolver.
Atenção aos espaços iniciais ou finais no Intervalo de valor de pesquisa, pois também podem causar falhas na procura. Considere combiná-lo com a função ARRANJAR se os seus dados puderem conter espaços extra.
Se o valor de procura for um número real (formato numérico), mas o campo correspondente na tabela estiver armazenado como texto, terá de converter o valor para texto antes de realizar a procura. A função TEXTO é adequada para este cenário:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Introduza isto na sua célula de destino, prima Entere o resultado correto será devolvido, tal como mostrado abaixo:
Aqui, o código de formato de número «0» dentro da função TEXTO assegura que o seu número seja convertido num valor Texto Simples antes da correspondência.
Se não tiver a certeza quanto aos formatos possíveis dos seus Intervalo de valor de pesquisa ou se esperar que ambos os Dividir por Texto e Número ocorram na sua coluna de procura, pode combinar ambas as abordagens utilizando a função SE.ERROpara gerir todas as possibilidades de forma transparente:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Introduza esta fórmula na sua célula de resultado. Primeiro tentará a procura convertendo o seu valor em número; se isso falhar (por exemplo, se o valor não puder ser convertido em número), tentará então converter o seu valor em texto e procurá-lo novamente. Isto é especialmente útil em conjuntos de dados com formatos mistos ou em ficheiros partilhados onde os padrões de introdução de dados não são uniformes.
Após introduzir qualquer uma das fórmulas acima, lembre-se de a copiar para as células adjacentes caso precise de a aplicar a vários intervalos de valores de pesquisa — basta selecionar a célula e arrastar a alça de preenchimento para baixo ou utilizar Ctrl+C e Ctrl+V conforme necessário. Em tabelas grandes, a utilização destas fórmulas ajuda a garantir correspondências fiáveis sem alterar a sua base de dados original.
Este método oferece uma solução flexível e universalmente aplicável para a maioria das procuras baseadas em folhas de cálculo. Contudo, em conjuntos de dados muito grandes ou quando necessita de processar muitos registos automaticamente, poderá considerar a utilização de ferramentas de automatização como o VBA para obter ainda maior eficiência.
Corrija rapidamente incompatibilidades de formato com Kutools para Excel
Se preferir uma solução mais rápida e sem fórmulas, o Kutools para Excel disponibiliza uma ferramenta intuitiva denominada Conversão entre texto e valor. Esta funcionalidade permite-lhe converter números armazenados como texto em números reais — ou vice-versa — com apenas alguns cliques. É especialmente útil para resolver problemas de formato antes de efetuar procuras como VLOOKUP ou CORRESP.
Após instalar o Kutools para Excel, proceda da seguinte forma.
- Selecione o intervalo que contém os seus dados problemáticos (por exemplo, números armazenados como texto).
- Aceda a «Kutools» > «Conteúdo» > «Conversão entre Texto e Valor».
- Na caixa de diálogo apresentada:
- Escolha «Texto para valor» se estiver a corrigir falhas de procura causadas por números formatados como texto.(Ou selecione «Valor para texto» se os Intervalo de valor de pesquisa estiverem armazenados como texto.)
- Clique em «OK» para converter de imediato o formato dos dados.
- Escolha «Texto para valor» se estiver a corrigir falhas de procura causadas por números formatados como texto.
Após converter Texto para valor, as células convertidas comportar-se-ão como números verdadeiros e deixarão de apresentar indicadores triangulares verdes de inconsistência.
Esta abordagem elimina a necessidade de colunas auxiliares, fórmulas ou VBA — sendo ideal para uma limpeza rápida antes de aplicar o VLOOKUP.
Kutools para Excel– Potencie o Excel com mais de 300 ferramentas essenciais, tornando o seu trabalho mais rápido e fácil, e aproveite as funcionalidades de IA para um processamento de dados mais inteligente e uma maior produtividade.Obtenha Já
Macro VBA: Normalizar formatos antes do VLOOKUP
Para utilizadores que trabalham regularmente com grandes conjuntos de dados, recebem ficheiros provenientes de fontes externas ou necessitam de automatização repetida, a utilização de uma macro VBA simples pode normalizar programaticamente o formato dos dados tanto na coluna de valor de procura como na coluna da tabela de procura. Desta forma, garante que todos os dados sejam convertidos quer em texto quer em número antes de executar o VLOOKUP, eliminando erros de correspondência devidos a incompatibilidades de formato. O VBA é particularmente útil para Processamento em Massa, evitando ajustes manuais e assegurando consistência dos dados através da automatização.
Vantagens: Automatiza a formatação em grandes intervalos ou fluxos de trabalho frequentes; reduz o risco de formatação em falta ou inconsistente; ideal para tarefas repetitivas.
Desvantagens: Não é adequado para utilizadores com restrições de macros ou pouco familiarizados com o uso de macros VBA.
Eis como pode utilizar uma macro para normalizar Formato de Célula:
1. Aceda ao separador Programador e clique em Visual Basic para abrir o editor do VBA. Na nova janela, clique em Inserir > Módulo e, de seguida, copie e cole o seguinte código na área do módulo:
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. Feche o editor do VBA. Para executar a macro, regresse ao Excel, prima Alt+F8, selecione StandardizeLookupFormats e clique em Executar.
Detalhes da operação e sugestões:
- Esta macro irá solicitar-lhe que selecione a coluna (seja o seu intervalo de procura ou o intervalo da tabela) que deseja normalizar.
- Após a seleção, perguntar-lhe-á se pretende converter o intervalo em números (clique em Sim) ou em texto (clique em Não). Selecione o mesmo formato tanto para a sua coluna de procura como para a coluna da tabela, de modo a garantir que o VLOOKUP coincida de forma fiável.
- Após executar esta macro, poderá ter de recalcular a folha de cálculo (prima)F9) ou reaplicar as suas fórmulas VLOOKUP, caso os resultados não apareçam imediatamente.
- Se receber um erro a indicar que as macros estão desativadas, ative as macros nas definições do Excel antes de continuar.
Esta solução é ideal para importações recorrentes de dados ou quando se pretende limpar colunas inconsistentes em grandes conjuntos de dados antes de aplicar VLOOKUP ou outras operações de procura.
Outros métodos incorporados no Excel: Utilize «Texto em Colunas» para corrigir formatos de dados
Uma forma rápida de alinhar números e aplicar Formato de Texto no Excel é utilizar a funcionalidade Texto em Colunas. Esta ferramenta incorporada é frequentemente usada para dividir dados, mas também permite forçar uma conversão de formato sem precisar de editar fórmulas — ideal para correções pontuais ou ao trabalhar com listas simples.
Vantagens: Muito fácil — sem necessidade de fórmulas nem código — e preserva a estrutura original dos dados.Desvantagens: Ideal para correções pontuais, mas não se atualiza automaticamente se os dados forem alterados.
Para utilizar este método para converter números armazenados como texto (ou vice-versa) numa coluna:
- Selecione a coluna cujo formato suspeita estar incorreto (por exemplo, a sua coluna de procura ou a coluna referenciada pelo VLOOKUP).
- No separador Dados, clique em Texto em Colunas.
- No assistente, selecione Delimitado e, em seguida, clique em Seguinte.
- Desmarque todas as caixas de seleção dos delimitadores (já que não vai dividir os dados) e clique em Seguinte.
- Em Formato dos Dados da Coluna, selecione Geral (para forçar o Excel a reconhecer números como números) ou selecione Texto (para converter valor em texto).
- Clique em Concluir para concluir o processo.
Após concluir, os seus dados terão o formato alinhado forçosamente como número ou texto, resolvendo incompatibilidades no VLOOKUP. Verifique sempre algumas células para confirmar que a conversão funcionou conforme esperado. Se necessário, repita o processo tanto na sua coluna de procura como na sua Intervalo de valor de pesquisa para garantir a máxima consistência.
Lembretes práticos: A função «Texto em Colunas» modifica diretamente os dados e pode substituir o conteúdo das células imediatamente à direita. Se tiver dúvidas, copie a sua coluna para uma área vazia antes de prosseguir e guarde sempre uma cópia de segurança do ficheiro antes de utilizar ferramentas de processamento em massa.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos