Como ordenar endereços por nome ou número de rua no Excel?
Ao gerir uma lista de endereços no Excel, é comum precisar organizar ou analisar os dados ordenando-os quer pelo nome da rua, quer pelo número da porta. Por exemplo, se pretender agrupar clientes que residem na mesma rua ou processar entregas por ordem crescente de números de porta, a ordenação por estes componentes torna-se essencial. Contudo, como os endereços costumam combinar nome e número da rua numa única célula, uma ordenação direta não produz os resultados desejados. Neste artigo, apresentamos métodos práticos para ordenar endereços por nome ou número de rua no Excel, exploramos os respetivos benefícios e cenários de aplicação, e disponibilizamos soluções alternativas e dicas de resolução de problemas adaptadas às diversas necessidades dos utilizadores.
Ordenar endereços por nome de rua com uma coluna auxiliar no Excel
Ordenar endereços por número de rua com uma coluna auxiliar no Excel
Ordenar endereços com VBA para extrair e ordenar automaticamente por nome ou número de rua
Ordenar endereços por nome ou número de rua com Power Query (sem colunas auxiliares)
Ordenar endereços por nome de rua com uma coluna auxiliar no Excel
Para ordenar endereços por nome de rua no Excel, comece por extrair apenas os nomes das ruas para uma coluna auxiliar. Esta abordagem é simples e funciona bem quando o formato dos endereços é consistente — como, por exemplo, «123 Apple St». É ideal para projetos rápidos ou listas de endereços simples.
1. Selecione uma coluna vazia junto à sua lista de endereços e introduza a seguinte fórmula na primeira célula dessa coluna auxiliar para extrair o nome da rua:
=MID(A1,FIND(" ",A1)+1,255)(Aqui, A1 refere-se à célula acima dos seus dados de endereço — ajuste caso os seus dados comecem noutra célula.)
Após introduzir a fórmula, prima Enter e, de seguida, arraste o identificador de preenchimento para aplicar a fórmula a todas as linhas da sua gama de endereços. Esta fórmula funciona localizando o primeiro espaço em cada endereço e devolvendo tudo o que vem após esse espaço — o nome da rua e qualquer sufixo. Certifique-se de que os seus endereços seguem a mesma estrutura; caso contrário, a fórmula poderá não dividir como esperado.
2. Destaque toda a coluna auxiliar (a coluna com os nomes de rua extraídos), aceda ao separador Dados e clique em Ascendente. Isto ordenará os nomes de rua por ordem alfabética crescente.
3. Na caixa de diálogo Aviso de Ordenação que aparece, selecione Expandir a seleção para garantir que toda a informação do endereço permanece junta durante a ordenação.
4. Clique em Ordenar. A sua Lista de Endereços será agora reordenada com base nos nomes de rua, agrupando ruas semelhantes.
Nota: Este método funciona melhor com endereços em formatos padronizados. Se as suas células contiverem padrões irregulares ou múltiplos espaços antes do nome da rua, poderá ter de ajustar a fórmula. Verifique sempre alguns resultados quanto à exatidão após utilizar a fórmula.
Vantagens: Simples e não requer ferramentas adicionais.
Desvantagens: Depende de uma formatação consistente; exige trabalho adicional se o formato do endereço variar.
Ordenar endereços por número de rua com uma coluna auxiliar no Excel
Se precisar de ordenar uma lista de endereços pelo número de porta — por exemplo, para definir uma ordem de entrega ou identificar moradas vizinhas — basta extrair os números e utilizá-los para ordenação. Esta abordagem é eficaz mesmo quando os endereços se encontram em ruas diferentes.
1. Numa célula vazia junto à sua Lista de Endereços, introduza a seguinte fórmula para extrair o número de porta:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Onde A1 é o primeiro endereço da sua lista — ajuste conforme necessário.) Prima Enter após introduzi-la. Esta fórmula funciona ao localizar o primeiro espaço e devolver os caracteres anteriores, convertendo-os num valor numérico. Se os seus endereços tiverem dígitos iniciais, como números de porta, esta fórmula funcionará corretamente. Em seguida, arraste o identificador de preenchimento para aplicar a fórmula ao resto da sua lista.
2. Selecione a coluna auxiliar que acabou de criar, aceda ao separador Dados e clique em Ascendente(ou em)Ordenar do menor para o maior nas versões mais recentes do Excel).
3. Na caixa de diálogo Aviso de Ordenação, escolha Expandir a seleção para ordenar linhas completas.
4. Clique em Ordenar para aplicar. Os seus endereços estão agora ordenados pelo número de porta extraído.
Dica:Se preferir manter o número de porta como texto ou não precisar de realizar ordenação numérica, pode também utilizar:
=LEFT(A1,FIND(" ",A1)-1)Esta versão extrai números como uma cadeia de texto.
Precauções: Se os endereços começarem por palavras em vez de números (como «Main Street5»), estas fórmulas não funcionarão como pretendido. Verifique cuidadosamente os seus dados de endereços antes de utilizar a fórmula.
Vantagens: Rápida e fácil de usar se o formato do endereço for simples.
Desvantagens: Não lida com endereços em que os nomes ou sufixos antecedem o número, nem com endereços que contenham vários números.
Código VBA – Automatize a ordenação de endereços extraindo nomes/números de rua e ordenando a lista com uma macro
Para quem trabalha com listas de endereços maiores e mais complexas, ou cujos dados incluem estruturas de endereços variáveis, automatizar o processo de ordenação com VBA pode ser altamente eficaz. O VBA permite-lhe extrair rapidamente nomes ou números de rua, ordenar automaticamente as suas listas de endereços e minimizar passos manuais. Esta solução é ideal sempre que precisar de realizar ordenações periodicamente ou pretender integrar a ordenação num fluxo de trabalho automatizado.
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 toda a lista com base nesses nomes. Com ligeiros ajustes, funciona igualmente para extrair e ordenar pelo número da rua.
1. Clique no separador Programador > 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 prima F5. A sua Lista de Endereços na coluna A será agora ordenada alfabeticamente por nome de rua.
Esta versão extrai apenas o número que aparece antes do primeiro espaço e ordena numericamente.
Resolução de problemas:
– Confirme se os endereços estão na coluna A ou atualize o código para a localização dos seus dados.
– Se os seus dados incluírem um cabeçalho, poderá ter de ajustar Header = xlYes para evitar ordenar a linha do cabeçalho.
– Crie sempre uma cópia de segurança antes de executar código VBA em massa.
Vantagens: Não requer colunas auxiliares e funciona perfeitamente com conjuntos de dados grandes ou ordenações repetitivas.
Desvantagens: A configuração inicial exige permissões para macros e conhecimentos básicos de VBA.
Outros métodos incorporados no Excel – Utilize o Power Query para dividir colunas de endereços e ordenar diretamente no Power Query sem colunas auxiliares
O Power Query, disponível nas versões modernas do Excel (Excel 2016 e posteriores, bem como no Microsoft 365), oferece uma forma flexível e sem fórmulas de dividir endereços em componentes como número e nome de rua. Esta solução é ideal se pretender evitar fórmulas e colunas auxiliares ou se os seus endereços seguirem formatos variáveis que fórmulas básicas não conseguem tratar eficazmente. O Power Query também guarda os seus passos, permitindo-lhe atualizá-los à medida que os seus dados crescem.
1. Selecione os seus dados de endereço e vá para o separador Dados, depois escolha De Tabela/Intervalo (crie uma tabela se solicitado).
2. Na janela do Power Query, selecione a coluna do endereço e clique em Dividir Coluna > Por Delimitador. Escolha Espaço como delimitador , e selecione o primeiro delimitador mais à esquerda para o tipo Dividir em.
3. Isto dividirá o endereço em duas colunas: o número da rua e o restante nome/endereço da rua. Renomeie as novas colunas conforme necessário.
4. Para ordenar, clique na seta no cabeçalho da coluna do nome ou do número da rua e selecione Ordenar Crescente ou Ordenar Decrescente.
5. Clique em Fechar e Carregar para inserir os resultados ordenados novamente na sua folha de cálculo.
Dicas adicionais:
- Se o seu padrão de endereços não for consistente, pode refinar ainda mais as colunas no Power Query com divisões personalizadas ou transformações.
- Os passos do Power Query são automaticamente registados, permitindo-lhe atualizar os dados facilmente sempre que a origem for alterada.
- Este método não altera os seus dados originais, reforçando assim a segurança dos registos originais.
Vantagens: Não altera permanentemente a sua folha; é robusto para padrões complexos de endereços; e não requer fórmulas para gerir.
Desvantagens: Requer Excel 2016 ou posterior; a interface pode ser desconhecida para utilizadores novos.
Resumo e sugestões para resolver problemas:
– Verifique sempre a consistência do formato dos seus endereços antes de aplicar fórmulas ou VBA.
– Reveja sempre os resultados da ordenação para confirmar que estão corretos, especialmente após utilizar colunas auxiliares ou código.
– Para dados com uma estrutura inesperada (como números ou nomes de ruas em falta no final), ajuste as fórmulas ou recorra ao Power Query para uma divisão mais robusta.
– Faça cópias de segurança regulares antes de utilizar VBA ou ferramentas avançadas de dados, evitando assim a perda acidental de informação.
– Escolha a solução ideal (fórmulas, VBA ou Power Query) com base no volume dos seus dados, na versão do Excel e no seu nível de conforto com cada ferramenta.
– Caso tenha dúvidas sobre qual método utilizar, o Power Query é geralmente a opção mais flexível e segura, permitindo edições não destrutivas.
Artigos relacionados:
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