☰

암호:

tag-icon • tag-icon microsoft-excel
암호:

다음 형식의 주소 열이 있습니다.

120 Lemon Street Columbus OH 92738 (Basketball Courts)

(120 Lemon Street)그리고 이를 거리 주소 , 도시 (Columbus), 주 (OH), 우편번호 (92738)및 설명 으로 나누어야 합니다.((Basketball Courts))

내가 할 수 있는 방법이 있나요? 모두 같은 상태이므로 문제가 되지 않습니다. 그들은 서로 다른 도시/마을에 있으며 우편번호도 다릅니다.

일부만 수행하는 방법을 알고 있는 경우에도 알려주세요. 도움을 주시면 감사하겠습니다!

답변1

User1282637님, 아래에 보여드릴 예시를 설정했습니다. 나는 단지 그것을 출발점으로 삼았을 뿐이므로 당신은 자신만의 완전한 답을 완성할 수 있습니다.

먼저 두 개의 목록을 설정했습니다. 하나는 모든 주 약어이고 다른 하나는 내가 여기에서 찾은 모든 허용되는 거리 접미사입니다.

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

여기에 이미지 설명을 입력하세요

나는 단순히 목록을 대문자로 시작하고 나머지는 소문자로 변환하기 위해 내가 나열한 공식을 사용했습니다.

다음은 단순히 적용되는 내용을 찾는 것입니다.

여기에 이미지 설명을 입력하세요

이 부분은 여러 가지 방법으로 수행할 수 있지만 예를 들어 이 방법으로 결정했습니다. 단순히 사용된 접미사와 일치하는 항목을 찾는 해당 행의 숫자를 나열합니다.

괄호를 사용하여 (농구장) 부분을 구분합니다.

여기에 이미지 설명을 입력하세요

귀하의 경우 "Basketball Courts"의 "Courts"도 거리 접미사이기 때문에 남은 내용을 표시합니다.

여기에 이미지 설명을 입력하세요

다음으로 거리 접미사인 문자열의 길이를 확인해야 하므로 다음을 사용합니다.

여기에 이미지 설명을 입력하세요

이...

여기에 이미지 설명을 입력하세요

마지막으로 거리만 표시할 수 있습니다.

여기에 이미지 설명을 입력하세요

지금은 도시와 주를 수행하지 않았지만 이 아이디어를 따르면 이를 달성할 수 있습니다. 또한 더 깔끔한 옵션을 원한다면 VBA에 대해 더 자세히 알아보세요. 나는 이것이 적어도 당신에게 이 문제를 해결하는 방법에 대한 몇 가지 아이디어를 가르쳐주기를 바랍니다.

답변2

User1282637은 이 작업을 수행할 수 있는 방법이 있는지 묻고 이를 수행하는 데 도움을 요청합니다. 문제는 Excel 역학이 아니라 데이터의 모호성을 처리하는 방법입니다. 우편번호와 설명을 구문 분석하는 것은 간단합니다(5자리와 9자리 우편번호가 혼합되어 있지 않은 경우). 어려운 문제는 거리와 도시를 분리하는 문제이므로 이에 집중하겠습니다. 이는 Excel 수식을 사용하여 단계별로 수행하는 방법이 아닙니다. 단지 문제에 대한 통찰력을 공유하고 어려운 부분에 대한 결과를 얻기 위한 접근 방식을 설명하는 것뿐입니다.

문제는 서로 다른 필드 사이에 구분 기호가 없다는 것입니다. 설명이나 ZIP을 떼어내는 것은 쉽게 식별할 수 있기 때문에 문제가 되지 않습니다. 문제는 거리가 끝나고 도시가 시작되는 곳을 결정하는 것입니다. 거리 부분에서 다음 변형을 고려하십시오(완전한 목록과는 거리가 멀음).

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

거리에 있는 "단어"의 수는 1~2개에서 최대 7~8개까지 다양하므로 구문 분석에 유용하지 않습니다. 거리 "유형"도 특별히 유용하지 않습니다. 거리 "유형"(거리, 대로, 대로, 길, 유료 도로, 차선, 법원, 원형, 테라스 등)에만 사용되는 단어는 대략 50-100개입니다. 이를 거리 유형에 대한 약어(올바른 것과 잘못된 것)의 사용과 결합하면 목록이 수백 개가 됩니다. 게다가 이 지정이 항상 거리 분야의 마지막 단어는 아닙니다. 거리는 식별하기 가장 어려운 부분이므로 논리적 접근 방식은 나머지를 식별한 다음 나머지가 거리가 되는 것입니다.

도시는 여러 단어로 구성될 수 있습니다. Washington Court House, OH는 세 단어입니다. 그런 다음 오하이오주 세인트 메리스와 같은 상황을 고려해보세요. "St"는 도시 이름의 일부이거나 거리 유형 지정입니다. 어느 분야에 들어가나요? 아니면 오하이오 주 사우스 유클리드(South Euclid)는 도시 이름의 "South" 부분입니까, 아니면 거리 주소의 일부인 방향입니까? 도시에는 문제가 있지만 이를 처리할 방법이 있습니다.

도시를 식별하기 위해 우편번호를 사용하는 것에도 문제가 있습니다. 도시 이름과 우편번호가 항상 1:1로 일치하는 것은 아닙니다.

문제를 공격하는 가장 실용적인 방법은 "사전"(도시 목록 및 우편 번호 디렉토리)을 사용하는 것입니다. 이는 주소에서 가장 명확한 부분입니다. 이는 온라인이나 우체국에서 찾을 수 있습니다. 비교를 수행하려면 데이터나 목록을 정리해야 할 수도 있습니다. 동일한 대문자 사용 스타일이 필요하며 데이터에 추가 공백이 있으면 정확한 일치가 불가능합니다.

귀하의 데이터나 목록이 약어를 사용하는 경우 이를 처리해야 합니다. 이러한 차이가 발견되면 축약되지 않은 것을 표준 약어로 번역하거나 약어 사전(온라인이나 USPS에서도 이용 가능)과 2차 일치를 수행합니다.

ZIP은 쉽게 구문 분석될 수 있으므로 시작하기에 좋은 곳입니다. 우편번호 디렉토리에 대해 우편번호 조회를 수행합니다. 결과가 ZIP 바로 앞의 단어 문자열과 정확하게 일치하는 경우 이는 레코드의 어느 부분이 도시 필드인지 식별합니다.

정확하거나 명확하게 일치하는 항목이 없으면 도시 이름 비교로 이동합니다. 도시 이름 목록을 반복합니다. 각 이름에 대해 포함된 단어 수를 확인하고 이를 ZIP 바로 앞의 단어 수와 비교합니다.

두 프로세스 중 하나를 통해 일치하는 항목을 얻으면 도시 왼쪽에 남아 있는 모든 것이 거리 주소입니다.

이러한 유형의 응용 프로그램은 스프레드시트보다 데이터베이스 응용 프로그램을 사용하는 것이 훨씬 쉽습니다. 어느 쪽이든 이 작업을 자동화된 방식으로 수행하는 것은 간단한 작업이 아니라는 것을 알 수 있습니다. 몇 가지 스프레드시트 수식으로는 이를 수행할 수 없습니다.

프로그래밍이 아무리 엄격하더라도 수동으로 구문 분석해야 하는 레코드가 있고 수동으로 수정해야 하는 구문 분석 오류가 있을 수 있습니다. 보유하고 있는 레코드 수를 표시하지 않습니다. 단순히 수동으로 수행하는 것이 작업량을 줄일 수 있습니다.

숫자가 커서 해야 한다면 목록을 추려낼 것입니다. 명확한 ZIP 일치와 같이 쉽게 기록을 일치시킵니다. 그런 다음 데이터의 양에 따라 자동화된 매치 프로그래밍이 어느 정도 진행되는지 제어하게 됩니다.

남아있는 기록에 대해서는 이미 ZIP과 설명을 떼어냈다는 가정 하에 수동 처리 속도를 높이는 방법이 있습니다. 기록을 보고 도시에 있는 "단어"의 수를 시각적으로 식별하는 것은 빠른 정신 작업입니다. 지정된 셀에 이를 입력하고 수식을 사용하여 공백 구분 수에 따라 도시에서 거리를 분할합니다(N = 총 공백 + 1 - 도시 이름의 단어 수인 N번째 공백에서 구분).

답변3

이것을 시도해 보세요. 이 간단한 접근 방식은 주소와 도시를 동일한 셀에 포함할 수 있다면 정말 효과적일 것입니다. if가 단 한 단어인 경우 도시를 추출하는 공식이 있지만 여러 단어로 구성된 도시(예: New York)의 경우 훨씬 더 복잡해집니다.

방식... 주소 및 도시: =LEFT(A2,FIND("OH",A2)-1) 주: =MID(A2,FIND("OH",A2),2) - 모든 것이 OH라고 말씀하셨기 때문에 간단하게 설명했습니다. 우편번호: =MID(A2,FIND("OH",A2)+3,5) 설명: =TRIM(MID(A2,FIND("OH",A2)+8,30))

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

여기에 이미지 설명을 입력하세요

답변4

암호:

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

설명: Alt+F11나타나는 창에 위 코드를 눌러 붙여넣습니다. 그런 다음 주소가 포함된 셀을 선택하고 코드를 붙여넣은 창으로 돌아가서 키를 누릅니다 F5.

이것이 효과가 있다면 귀하의 특정 상황에 더 구체적으로 적용할 수 있도록 노력할 수 있습니다.

관련 정보