Como ordenar endereços por nome/número da rua no Excel?
Ao gerenciar uma lista de endereços no Excel, muitas vezes é necessário organizar ou analisar os dados ordenando os endereços pelo nome da rua ou pelo número da rua. Por exemplo, se você precisar agrupar clientes que moram na mesma rua ou processar entregas em ordem numérica das casas, ordenar por esses componentes é essencial. No entanto, como os formatos típicos de endereço misturam nomes e números da rua em uma única célula, a ordenação direta não dará os resultados esperados. Neste artigo, discutiremos métodos práticos para ordenar endereços por nome ou número da rua no Excel, analisaremos seus benefícios, cenários de aplicação e ofereceremos soluções alternativas e resolução de problemas para diversas necessidades de usuários.
Ordenar endereços pelo nome da rua com uma coluna auxiliar no Excel
Ordenar endereços pelo número da rua com uma coluna auxiliar no Excel
Ordenar endereços usando VBA para extrair e ordenar automaticamente por nome ou número da rua
Ordenar endereços por nome ou número da rua com Power Query (sem colunas auxiliares)
Ordenar endereços pelo nome da rua com uma coluna auxiliar no Excel
Para ordenar endereços pelo nome da rua no Excel, primeiro será necessário extrair apenas os nomes das ruas para uma coluna auxiliar. Essa abordagem é simples e funciona bem quando o formato do endereço é consistente, como "Rua Maçã, 123". É adequada para projetos rápidos ou listas de endereços simples.
1. Selecione uma coluna vazia adjacente à sua lista de endereços. Insira a seguinte fórmula na primeira célula da coluna auxiliar para extrair o nome da rua:
=MID(A1,FIND(" ",A1)+1,255)(Aqui, A1 refere-se à célula superior dos seus dados de endereço—ajuste se seus dados começarem em outro lugar.)
Após digitar a fórmula, pressione Enter e depois arraste a alça de preenchimento para baixo para aplicar a fórmula a todas as linhas no intervalo de endereços. Esta fórmula funciona localizando o primeiro espaço em cada endereço e retornando tudo depois desse espaço — o nome da rua e qualquer sufixo. Certifique-se de que seus endereços sigam a mesma estrutura; caso contrário, a fórmula pode não dividir corretamente.
2. Destaque toda a coluna auxiliar (a coluna com os nomes de ruas extraídos), vá até a aba Dados e clique em Classificar de A a Z. Isso classificará os nomes das ruas em ordem crescente (alfabética).
3. Na caixa de diálogo Aviso de Classificação que aparece, selecione Expandir a seleção para garantir que as informações completas do endereço permaneçam juntas durante a ordenação.
4. Clique em Classificar. Sua lista de endereços agora será reordenada com base nos nomes das ruas, fazendo com que ruas semelhantes apareçam juntas.
Nota: Esse método funciona melhor com formatos de endereços padronizados. Se suas células de endereço contiverem padrões irregulares ou múltiplos espaços antes do nome da rua, a fórmula pode precisar ser ajustada. Sempre verifique alguns resultados para garantir a precisão após usar a fórmula.
Vantagens: Simples, não requer ferramentas adicionais.
Desvantagens: Depende de formatação consistente; trabalho extra é necessário se o formato do endereço variar.
Ordenar endereços pelo número da rua com uma coluna auxiliar no Excel
Se você precisa ordenar uma lista de endereços pelo número numérico da rua — como ao atribuir uma ordem de entrega ou identificar endereços vizinhos —, é fácil extrair o número e utilizá-lo para ordenar. Isso também é eficaz mesmo quando os endereços estão em diferentes ruas.
1. Em uma célula em branco ao lado da sua lista de endereços, insira a seguinte fórmula para extrair o número da rua:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Onde A1 é o primeiro endereço na sua lista — ajuste conforme necessário.) Pressione Enter após digitá-la. Essa fórmula funciona localizando o primeiro espaço e retornando os caracteres antes dele, convertendo-os para um valor numérico. Se seus endereços tiverem dígitos iniciais como números de rua, essa fórmula funcionará corretamente. Em seguida, arraste a alça de preenchimento para baixo para aplicar a fórmula ao restante da sua lista.
2. Selecione a coluna auxiliar que você acabou de criar, vá até a aba Dados e clique em Classificar de A a Z (ou Classificar do Menor para o Maior em versões mais recentes do Excel).
3. No diálogo Aviso de Classificação, escolha Expandir a seleção para ordenar linhas inteiras.
4. Clique em Classificar para aplicar. Os seus endereços agora estão ordenados pelo número da rua extraído.
Dica: Se preferir manter o número da rua como texto, ou não precisar realizar ordenação numérica, você também pode usar:
=LEFT(A1,FIND(" ",A1)-1)Essa versão extrai o número como uma sequência de texto.
Precauções: Se os endereços começarem com palavras em vez de números (como "Rua Principal 5"), essas fórmulas não funcionarão conforme o esperado. Verifique atentamente seus dados de endereço antes de usar a fórmula.
Vantagens: Rápido e amigável se o formato do endereço for simples.
Desvantagens: Não lida com endereços com nomes/sufixos anteriores ao número, nem endereços com múltiplos números.
Código VBA - Automatizar a ordenação de endereços extraindo nomes/numeros de ruas e ordenando a lista com uma macro
Para aqueles que trabalham com listas de endereços maiores e mais complexas, ou cujo dado inclui estruturas variáveis de endereços, automatizar o processo de ordenação usando VBA pode ser altamente eficaz. O VBA permite que você extraia rapidamente nomes ou números de ruas, ordene automaticamente sua lista de endereços e minimize etapas manuais. Essa solução é adequada quando você periodicamente precisa realizar ordenações, ou deseja integrar a ordenação em um fluxo de trabalho.
Nota: Esta macro VBA extrai o nome da rua (a parte após o primeiro espaço) de cada endereço na coluna A e ordena a lista inteira com base nesses nomes. Ela também funciona para extrair e ordenar por número da rua com pequenos ajustes.
1. Clique na aba Desenvolvedor > Visual Basic. Na janela que aparece, clique em Inserir > Módulo, e cole o seguinte código VBA na janela do módulo:
Sub SortAddressesByStreetName()
Dim ws As Worksheet
Dim lastRow As Long
Dim tempCol As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
' Create helper column with street names
For i = 1 To lastRow
ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
Next i
' Sort the whole data range by the helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
.Header = xlNo
.Apply
End With
' Delete helper column
ws.Columns(tempCol).Delete
End Sub2. Para executar o código, com a lista de endereços ativa, clique no 
botão ou pressione F5. Sua lista de endereços na coluna A agora será ordenada alfabeticamente por nome da rua.
Essa versão extrai apenas o número antes do primeiro espaço e ordena por ordem numérica.
Resolução de Problemas:
- Confirme que os endereços estão na coluna A ou atualize o código para o local de seus dados.
- Se seus dados incluem um cabeçalho, talvez seja necessário ajustar Header = xlYes para evitar ordenar a linha de cabeçalho.
- Sempre crie um backup antes de executar código VBA em massa.
Vantagens: Nenhuma coluna auxiliar necessária; funcional para grandes conjuntos de dados ou ordenações repetitivas.
Desvantagens: Configuração inicial requer permissões de macro e entendimento básico de VBA.
Outros Métodos Integrados do Excel - Use Power Query para dividir colunas de endereços e ordenar diretamente dentro do Power Query sem colunas auxiliares
Power Query, disponível nas versões modernas do Excel (Excel 2016 e posterior, assim como Microsoft 365), fornece uma maneira flexível e sem fórmulas de dividir endereços em componentes como número e nome da rua. Essa solução é ideal se você prefere evitar fórmulas e colunas auxiliares, ou se seus endereços seguem formatos variados que fórmulas básicas não podem lidar eficientemente. Power Query também pode salvar seus passos para que você possa atualizá-los conforme seus dados crescem.
1. Selecione seus dados de endereço e vá até a aba Dados , depois escolha De Tabela/Faixa (crie uma tabela se solicitado).
2. Na janela Power Query, selecione sua coluna de endereço, depois clique Dividir Coluna > Por Delimitador. Escolha Espaço como delimitador, e selecione o primeiro delimitador mais à esquerda para o Dividir em tipo.
3. Isso dividirá o endereço em duas colunas: o número da rua e o restante do nome/endereço da rua. Renomeie as novas colunas conforme necessário.
4. Para ordenar, clique na seta no cabeçalho da coluna de nomes ou números da rua e selecione Classificar Crescente ou Classificar Decrescente.
5. Clique Fechar & Carregar para inserir os resultados ordenados de volta na sua planilha.
Dicas Extras:
- Se o padrão de seu endereço não for consistente, você pode manipular ainda mais as colunas no Power Query usando divisões personalizadas ou transformações.
- Os passos do Power Query são gravados automaticamente; você pode atualizar os dados facilmente se sua fonte mudar.
- Esse método não altera seus dados originais, aumentando a segurança dos registros originais.
Vantagens: Nenhuma alteração permanente em sua planilha; robusto para padrões complexos de endereços; sem fórmulas para gerenciar.
Desvantagens: Requer Excel 2016 ou versão mais recente; interface pode ser desconhecida para novos usuários.
Resumo e sugestões de resolução de problemas:
- Lembre-se de verificar a consistência do formato dos seus endereços antes de aplicar fórmulas ou VBA.
- Sempre visualize os resultados da ordenação para confirmar a correção, especialmente após usar colunas auxiliares ou código.
- Para dados com estrutura inesperada (como números ausentes ou nomes de ruas no final), ajuste fórmulas ou considere o Power Query para divisões mais robustas.
- Faça backups regulares antes de usar VBA ou ferramentas avançadas de dados para evitar perda acidental de dados.
- Escolha uma solução (fórmulas, VBA, Power Query) que se alinhe melhor com seu volume de dados, versão do Excel e nível de conforto com a ferramenta.
- Se não tiver certeza de qual método é o melhor, o Power Query geralmente oferece a maior flexibilidade e é mais seguro para edições não destrutivas.
Artigos Relacionados:
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