Código:

Question

Eu tenho uma coluna endereçada neste formato:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

E preciso dividir em: endereço (120 Lemon Street), cidade (Columbus), estado (OH), CEP (92738)e descrição((Basketball Courts))

Existe uma maneira de fazer isso? Todos estes estão no mesmo estado, então isso não é um problema. Eles estão em cidades/vilas diferentes e têm códigos postais diferentes.

Se você só sabe fazer parte, me avise. Qualquer ajuda é apreciada, obrigado!

Answer 1

User1282637, configurei um exemplo para mostrar abaixo. Eu apenas fiz disso um ponto de partida para que você possa terminar com sua própria resposta completa.

Primeiro, configurei duas listas. Uma era todas as abreviações de estado e a outra eram todos os sufixos de rua aceitos que encontrei aqui:

http://pe.usps.com/text/pub28/28apc_002.htm

Usei a fórmula que listei simplesmente para converter a lista para começar com uma letra maiúscula e depois o resto para minúscula, como você escreveu a sua.

Em seguida, basta procurar o que se aplica.

Esta parte poderia ser feita de várias maneiras, mas decidi usar esta como exemplo. Ele simplesmente listará um número na linha correspondente que encontra uma correspondência para o sufixo usado.

Separo a parte (Quadras de Basquete) usando os parênteses:

Eu exibo o que resta porque no seu caso "Courts" de "Basketball Courts" também é um sufixo de rua:

Em seguida, preciso descobrir o comprimento da string que é o sufixo da rua, então uso o seguinte:

e isto...

e por último isso me permite exibir apenas a rua:

Agora eu não fiz a cidade e o estado, mas seguindo essa ideia você consegue isso. Além disso, se você quiser uma opção mais limpa, definitivamente procure aprender mais sobre o VBA. Espero que isso pelo menos lhe ensine algumas idéias de como fazer isso.

Answer 2

User1282637 pergunta se existe uma maneira de realizar esta tarefa e solicita ajuda para fazer isso. A questão não é a mecânica do Excel, mas sim como lidar com a ambigüidade nos dados. A análise do CEP e da descrição é simples (a menos que você tenha uma combinação de CEPs de 5 e 9 dígitos). O problema difícil está em separar a rua da cidade, então vou focar nisso. Isto não pretende ser um passo a passo de como fazer isso com fórmulas do Excel. Trata-se apenas de compartilhar alguns insights sobre o problema e descrever uma abordagem para chegar a um resultado para a parte difícil.

O problema é que não há delimitadores entre os diferentes campos. Isso não é um problema para retirar a descrição ou o ZIP porque eles são facilmente identificáveis. O problema é determinar onde termina a rua e começa a cidade. Considere estas variações na parte Street (longe de ser uma lista exaustiva):

120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3

O número de "palavras" na rua pode variar de 1 ou 2 a 7 ou 8, portanto isso não é útil para análise. O "tipo" de rua também não é especialmente útil. Existem cerca de 50-100 palavras usadas apenas para o "tipo" de rua (rua, avenida, avenida, caminho, pedágio, viela, quadra, círculo, terraço, etc.). Combine isso com o uso de abreviaturas para o tipo de rua, tanto adequadas quanto incorretas, e a lista estará na casa das centenas. Além disso, essa designação nem sempre é a última palavra no campo das ruas. A rua é a parte mais difícil de identificar, então a abordagem lógica é identificar o resto e então o resto é a rua.

A cidade pode ter várias palavras. Washington Court House, OH tem três palavras. Então considere situações como St Marys, OH. O “St” faz parte do nome da cidade ou é uma designação de tipo de rua; em qual campo ele entra? Ou South Euclid, OH - "Sul" faz parte do nome da cidade ou é um direcional que faz parte do endereço? A cidade tem seus problemas, mas há uma maneira de lidar com eles.

Até usar o CEP para identificar a cidade tem problemas. Nem sempre há uma correspondência de 1:1 entre o nome da cidade e o CEP.

A maneira mais prática de atacar o problema é usar “dicionários”: uma lista de cidades e um diretório de CEPs. Esta é a parte mais inequívoca do endereço. Eles podem ser encontrados online ou nos Correios. Para fazer comparações, pode ser necessário limpar seus dados ou as listagens. Eles precisarão do mesmo estilo de letras maiúsculas e quaisquer espaços extras em seus dados impedirão uma correspondência exata.

Se seus dados ou a listagem usarem abreviações, você precisará lidar com isso. Traduza o não abreviado para as abreviaturas padrão ou faça uma correspondência secundária com um dicionário de abreviações (também disponível online ou nos Correios), quando essas diferenças forem encontradas.

O ZIP pode ser facilmente analisado e esse é um bom lugar para começar. Faça uma pesquisa de CEP no diretório de CEP. Se o resultado corresponder exatamente a uma sequência de palavras imediatamente anterior ao ZIP, isso identificará qual parte do registro é o campo da cidade.

Se não houver uma correspondência exata ou inequívoca, prossiga para uma comparação dos nomes das cidades. Itere pela lista de nomes de cidades. Para cada nome, determine o número de palavras que ele contém e compare-o com o número de palavras imediatamente anteriores ao ZIP.

Se você conseguir uma correspondência por meio de qualquer um dos processos, tudo o que resta à esquerda da cidade será o endereço.

Esse tipo de aplicação é muito mais fácil de fazer com um aplicativo de banco de dados do que com uma planilha. De qualquer forma, você pode perceber que tentar fazer isso de forma automatizada não é uma tarefa simples. Você não pode fazer isso com algumas fórmulas de planilha.

Não importa quão rigorosa seja sua programação, é provável que você ainda tenha registros que precisa analisar manualmente e erros de análise que precisa corrigir manualmente. Você não indica quantos registros possui. Pode ser menos trabalhoso simplesmente fazer isso manualmente.

Se o número for grande e eu tiver que fazer isso, selecionarei a lista. Combine os registros de maneira fácil, como correspondências ZIP inequívocas. Em seguida, deixe que a quantidade de dados governe o quão longe você vai programar correspondências automatizadas.

Para os registros que sobraram, supondo que você já tenha retirado o ZIP e a descrição, aqui está uma forma de agilizar o processo manual. Observe o registro e identifique visualmente a quantidade de “palavras” na cidade, o que é uma tarefa mental rápida. Insira isso em uma célula prescrita e use uma fórmula para separar a rua da cidade com base no número de intervalos de espaço (separe no enésimo espaço onde N = total de espaços + 1 - número de palavras no nome da cidade).

Answer 3

Experimente isso. Essa abordagem simples deve funcionar muito bem se você conseguir conviver com o endereço e a cidade na mesma célula. Eu tenho uma fórmula para extrair a cidade se for apenas uma palavra, mas fica muito mais complexa se houver cidades com várias palavras (por exemplo, Nova York).

Fórmulas... Endereço e cidade: =LEFT(A2,FIND("OH",A2)-1) Estado: =MID(A2,FIND("OH",A2),2) - você mencionou que tudo é OH, então mantive as coisas simples CEP: =MID(A2,FIND("OH",A2)+3,5) Descrição: =TRIM(MID(A2,FIND("OH",A2)+8,30))

https://onedrive.live.com/redir?page=view&resid=D91C36B074F4D0F6!3224&authkey=!AO_MbW7Qxv4yWDo

Answer 4

Código:

Sub SplitAddress()
    Dim Addr As String
    Dim l As Integer
    Dim Desc As String
    Dim Zip As String
    Dim State As String
    Dim City As String
    
    Addr = Selection
    
    l = InStrRev(Addr, "(")
    Desc = Right(Addr, Len(Addr) - l + 1)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    Zip = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    State = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    City = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    Selection.Offset(0, 1) = Addr
    Selection.Offset(0, 2) = City
    Selection.Offset(0, 3) = State
    Selection.Range("B11").Offset(0, 4) = Zip
    Selection.Range("B11").Offset(0, 5) = Desc
End Sub

Explicação: Pressione Alt+F11e cole o código acima na janela que aparece. Em seguida, selecione uma célula que contenha o endereço e retorne à janela onde colou o código e pressione F5.

Se isso funcionar, podemos trabalhar para torná-lo mais específico para sua situação específica.