Como fazer a pesquisa vertical de números armazenados como texto no Excel?
Ao usar VLOOKUP no Excel, encontrar formatos incompatíveis – especificamente, quando um valor de pesquisa é armazenado como texto enquanto a coluna de pesquisa contém números, ou vice-versa – pode levar a falhas ou erros na busca. Essa incompatibilidade de formatação é um problema comum, especialmente quando os dados vêm de fontes externas, são importados ou quando se trabalha com grandes conjuntos de dados colaborativos. Resolver essas incompatibilidades é essencial para garantir que o VLOOKUP funcione conforme o esperado e ajude você a recuperar as informações corretas. Este guia passo a passo apresenta várias soluções práticas para abordar essas inconsistências de formatação, garantindo buscas confiáveis e precisas, independentemente de como os números estão armazenados em sua pasta de trabalho.
Este artigo demonstrará maneiras eficazes de lidar com esses erros, incluindo ajustes de fórmulas, ferramentas internas do Excel e automação por VBA para processamento em massa ou automatizado. Também discutimos os benefícios e considerações de cada método, ajudando você a escolher a abordagem mais adequada para o seu cenário.
- Pesquisar números armazenados como texto com fórmulas
- Corrigir rapidamente incompatibilidades de formato com Kutools para Excel
- Macro VBA: Padronizar Formatos Antes do VLOOKUP
- Outros Métodos Internos do Excel: Use 'Texto para Colunas' para Corrigir Formatos de Dados
Pesquisar números armazenados como texto com fórmulas
Se seus dados de pesquisa incluem números armazenados como texto em um lugar e números reais em outro, o VLOOKUP pode falhar ao encontrar correspondências devido a essa inconsistência de formato. Uma das soluções mais diretas é usar fórmulas do Excel que convertem o valor de pesquisa ou a coluna de pesquisa para um formato consistente em tempo real. Essa abordagem funciona bem na maioria das operações de planilha, é fácil de aplicar e mantém seus dados originais inalterados.
Por exemplo, se o seu valor de pesquisa está armazenado como texto, enquanto o campo correspondente na tabela está formatado como número, você pode usar a função VALUE para converter o texto em um número dentro da fórmula VLOOKUP.
Insira a seguinte fórmula em uma célula em branco onde você deseja exibir o resultado:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Após inserir a fórmula, pressione a tecla Enter para recuperar o valor correspondente aos seus critérios, conforme ilustrado na captura de tela abaixo:
Explicação dos parâmetros e dicas:
- G1: A célula que contém o valor que você deseja procurar (pode ser texto ou número).
- A2:D15: O intervalo de sua tabela de dados que inclui a coluna de pesquisa e as colunas que contêm as informações que você deseja retornar.
- 2: O número da coluna (a partir da coluna mais à esquerda do intervalo da tabela) para o resultado que você deseja retornar.
Tenha cuidado para verificar espaços à esquerda/direita nos valores de pesquisa, pois eles também podem resultar em falhas de busca. Considere combinar a função TRIM se seus dados puderem conter espaços extras.
Se o valor de pesquisa for um número real (formato numérico), mas o campo correspondente na tabela estiver armazenado como texto, será necessário converter o número em texto antes de realizar a busca. A função TEXT é adequada para esse cenário:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Insira isso em sua célula de destino, pressione Enter e o resultado correto será retornado conforme mostrado abaixo:
Aqui, o código de formato numérico “0” dentro da função TEXT garante que o número seja convertido em um valor de texto simples antes do casamento.
Se você não tiver certeza sobre os possíveis formatos de seus valores de pesquisa, ou se espera que tanto texto quanto números ocorram em sua coluna de pesquisa, você pode aninhar ambas as abordagens usando a função IFERROR para lidar com todas as possibilidades de forma contínua:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Insira esta fórmula em sua célula de resultado. Ela primeiro tentará a pesquisa convertendo seu valor em um número; se isso falhar (por exemplo, se o valor não puder ser transformado em um número), ela então tentará converter seu valor em texto e buscar novamente. Isso é especialmente útil em conjuntos de dados com formatos misturados ou em arquivos compartilhados onde os padrões de entrada de dados não são uniformes.
Após inserir qualquer uma das fórmulas acima, lembre-se de copiar a fórmula para as células adjacentes, se precisar aplicá-la a vários valores de pesquisa – basta selecionar a célula, arrastar a alça de preenchimento para baixo ou usar Ctrl+C e Ctrl+V conforme necessário. Para tabelas grandes, o uso dessas fórmulas ajuda a garantir correspondências confiáveis sem alterar seu banco de dados original.
Esse método oferece uma solução flexível e universalmente aplicável para a maioria das pesquisas baseadas em planilhas. No entanto, para conjuntos de dados muito grandes ou quando você precisa processar muitos registros automaticamente, pode-se considerar o uso de ferramentas de automação como VBA para maior eficiência.
Corrigir rapidamente incompatibilidades de formato com Kutools para Excel
Se você preferir uma solução mais rápida, sem fórmulas, o Kutools para Excel oferece uma ferramenta amigável chamada Converter entre Texto e Número. Esse recurso permite que você converta 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 realizar buscas como VLOOKUP ou CORRESP.
Após instalar o Kutools para Excel, siga os passos abaixo.
- Selecione o intervalo que contém seus dados problemáticos (por exemplo, números armazenados como texto).
- Vá para "Kutools" > "Conteúdo" > "Converter entre Texto e Número".
- Na caixa de diálogo pop-up:
- Escolha "Texto para número" se você estiver corrigindo falhas de busca devido a números formatados como texto. (Ou selecione "Número para texto" se os valores de pesquisa estiverem armazenados como texto.)
- Clique em "OK" para converter imediatamente o formato dos dados.
- Escolha "Texto para número" se você estiver corrigindo falhas de busca devido a números formatados como texto.
Após converter texto em números, as células convertidas se comportarão como números verdadeiros e não mostrarão mais indicadores de triângulo verde para inconsistência.
Essa abordagem elimina a necessidade de colunas auxiliares, fórmulas ou VBA – tornando-a ideal para uma limpeza rápida antes de aplicar VLOOKUP.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Macro VBA: Padronizar Formatos Antes do VLOOKUP
Para usuários que regularmente trabalham com grandes conjuntos de dados, recebem arquivos de fontes externas ou exigem automação repetida, usar uma macro VBA simples pode programaticamente padronizar o formato de dados tanto na coluna do valor de pesquisa quanto na coluna da tabela de pesquisa. Assim, você garante que todos os dados sejam convertidos para texto ou número antes de executar o VLOOKUP, eliminando erros de correspondência devido à incompatibilidade de formato. O VBA é especialmente útil para processamento em massa, economizando ajustes manuais e garantindo consistência de dados por meio de automação.
Vantagens: Automação de formatação para grandes intervalos de dados ou fluxos de trabalho frequentes; minimiza o risco de formatação ausente ou inconsistente; adequado para tarefas repetitivas.
Desvantagens: Não é adequado para usuários com restrições de macros ou aqueles não familiarizados com o uso de macros VBA.
Aqui está como você pode usar uma macro para padronizar formatos de células:
1. Vá para a guia Desenvolvedor e clique em Visual Basic para abrir o editor VBA. Na nova janela, clique em Inserir > Módulo, depois 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 VBA. Para executar a macro, retorne ao Excel, pressione Alt+F8, selecione StandardizeLookupFormats e clique em Executar.
Detalhes da operação e dicas:
- Essa macro solicitará que você selecione a coluna (seja sua faixa de PESQUISA ou faixa de TABELA) que você deseja padronizar.
- Após a seleção, ela perguntará se você deseja converter o intervalo para números (clique em Sim) ou para texto (clique em Não). Selecione o mesmo formato para suas colunas de pesquisa e tabela para garantir que o VLOOKUP corresponda de forma confiável.
- Após executar essa macro, pode ser necessário recalcular a planilha (pressione F9) ou reaplicar suas fórmulas VLOOKUP se os resultados não aparecerem imediatamente.
- Se você receber um erro informando que as macros estão desativadas, habilite as macros nas configurações do Excel antes de prosseguir.
Essa solução é ideal para importações de dados recorrentes ou quando você estiver limpando colunas inconsistentes em grandes conjuntos de dados antes de aplicar VLOOKUP ou outras operações de busca.
Outros Métodos Internos do Excel: Use 'Texto para Colunas' para Corrigir Formatos de Dados
Uma maneira rápida de alinhar formatos de números e texto no Excel é usar o recurso Texto para Colunas. Essa ferramenta interna é frequentemente usada para dividir dados, mas também pode forçar uma conversão de formato sem editar fórmulas, sendo útil quando você quer uma correção única ou ao lidar com listas simples.
Prós: Muito fácil, sem fórmulas ou código necessário, preserva a estrutura de dados original; Contras: Ideal para correções únicas, não atualiza automaticamente se os dados mudarem.
Para usar este método para converter números armazenados como texto (ou vice-versa) em uma coluna:
- Selecione a coluna com o formato suspeito de incompatibilidade (por exemplo, sua coluna de pesquisa ou a coluna referenciada pelo VLOOKUP).
- Na guia Dados, clique em Texto para Colunas.
- No assistente, escolha Delimitado e clique em Avançar.
- Desmarque todas as caixas de seleção de delimitadores (já que você não está dividindo os dados); clique em Avançar.
- Em Formato de Dados de Coluna, selecione Geral (para forçar o Excel a reconhecer números como números) ou selecione Texto (para converter números em texto).
- Clique em Concluir para finalizar o processo.
Uma vez concluído, seus dados terão seu formato forçadamente alinhado para número ou texto, resolvendo incompatibilidades no VLOOKUP. Sempre verifique algumas células para confirmar que a conversão funcionou conforme o esperado. Se necessário, repita o processo para sua coluna de pesquisa e seus valores de pesquisa para maximizar a consistência.
Lembretes práticos: “Texto para Colunas” modifica os dados diretamente, podendo sobrescrever o conteúdo das células se houver dados existentes imediatamente à direita. Considere copiar sua coluna para uma área em branco primeiro, caso não tenha certeza, e sempre salve um backup de seu arquivo antes de usar ferramentas de manipulação de dados em massa.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins