Capítulo 17 · 17.11
Extrair a rua, o número de porta e o complemento de uma única coluna de endereços
Os endereços de uma loja online, de uma gestão de sócios ou de um ficheiro de clientes antigo estão frequentemente numa única coluna: "Dorpsstraat 12-2". Para etiquetas de envio, um mailing ou uma importação numa transportadora, a rua, o número de porta e o complemento têm de estar separados. Com Texto para colunas isso não funciona bem, porque os próprios nomes das ruas contêm espaços. A separação do nome próprio e do apelido é explicada em Separar nome próprio e apelido de uma coluna; os endereços são mais complicados. Para isso, o Euro-Office dispõe das funções REGEX.EXTRAIR e REGEX.SUBSTITUIR, que trabalham com um padrão de pesquisa.
Como exemplo, utilizamos a lista de clientes da Bloemisterij Het Zonnetje, com os seguintes dados na coluna A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Passo 1: o número de porta com o complemento
Escreva em B2:
=REGEX.EXTRAIR(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
O padrão entre aspas significa: um ou mais algarismos (\d+), seguidos eventualmente de caracteres sem espaço (\S*, como -2 ou B), seguidos eventualmente de um espaço com 1 a 3 letras (como " a" ou " bis"), e tudo isto no final do texto ($). Resultado: 15 a, 12-2, 8B, 7 e 120 bis. Os anos em "Laan 1940-1945" ficam de fora, porque só conta a última parte do endereço.
Passo 2: a rua
Escreva em C2:
=COMPACTAR(REGEX.SUBSTITUIR(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEX.SUBSTITUIR substitui essa mesma parte por nada, e COMPACTAR remove o espaço no final. Fica com Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 e assim por diante.
Passo 3: o número e o complemento em separado
Se o número e o complemento também tiverem de ficar separados, continue a trabalhar com o resultado do passo 1 em B2:
- Apenas o número, em D2: =REGEX.EXTRAIR(B2;"^\d+") (os algarismos no início): 15, 12, 8, 7, 120.
- O complemento, em E2: =COMPACTAR(REGEX.SUBSTITUIR(B2;"^\d+[- ]?";"")): a, 2, B, nada, bis.
Em seguida, arraste as fórmulas até à última linha. Ou trabalhe por coluna: selecione, por exemplo, B2:B6, escreva a fórmula e prima Ctrl+Enter; a fórmula fica de uma só vez em todas as células selecionadas.

Um endereço com vírgulas
Se o endereço completo estiver numa única célula, como "Dorpsstraat 12, 1234 AB Utrecht", =DIVIDIRTEXTO(A9;", ") divide-o pela vírgula: "Dorpsstraat 12" fica na própria célula e "1234 AB Utrecht" na célula ao lado. Depois, separa o código postal e a localidade com TEXTOANTES e TEXTODEPOIS, que devolvem o texto antes ou depois da n-ésima ocorrência de um delimitador. Se "1234 AB Utrecht" estiver em C9:
- Código postal: =TEXTOANTES(C9;" ";2) devolve 1234 AB (tudo antes do segundo espaço).
- Localidade: =TEXTODEPOIS(C9;" ";2) devolve Utrecht.
Terminou? Converta em valores fixos
As colunas separadas são fórmulas que dependem da coluna A. Se pretender eliminar a coluna A, ou enviar o ficheiro a alguém com uma versão mais antiga do Excel, converta os resultados em texto simples: selecione as colunas, copie-as com Ctrl+C e cole-as no mesmo local com Ctrl+V. Por baixo das células coladas aparece um pequeno botão (Ctrl). Clique nele e, em Valor, escolha Colar apenas os valores.

Armadilhas
- Exceções continuam a existir: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Aplique um filtro às colunas e procure resultados vazios ou estranhos antes de utilizar a lista.
- Maiúsculas: o padrão [A-Za-z] abrange letras minúsculas e maiúsculas. Se utilizar apenas [a-z], 8B fica de fora.
- #N/D em REGEX.EXTRAIR significa que o padrão não ocorre, por exemplo num endereço sem número de porta.
- Outras folhas de cálculo: o Excel só conhece estas funções REGEX nas versões recentes do Microsoft 365. Um colega com uma versão mais antiga verá #NOME?. Nesse caso, converta primeiro os resultados em valores.
- Também é possível sem REGEX, mas de forma mais limitada: =TEXTODEPOIS(A2;" ";-1) devolve tudo o que está depois do último espaço. Isto funciona para "Kerkplein 7" e "Dorpsstraat 12-2", mas em "15 a" obtém apenas o a.
