Dan에는 셀 열이 있고 각 셀에는 도시, 주 및 우편 번호의 세 항목이 있습니다. (주소 줄에서 볼 수있는 것처럼 세 가지 모두 단일 셀에 있습니다.) 일부 우편 번호는 5 자리이고 일부는 9 자리입니다. Dan은 현재 데이터 오른쪽에있는 자체 셀에 2 자 주와 5 자리 우편 번호를 모두 가져와야합니다. Dan은 Text to Columns 도구를 사용할 수 있다는 것을 알고 있지만 여러 단어로 된 도시 이름과 쉼표를 처리해야하므로 많은 작업이 필요하다고 생각합니다. 댄은 도울 수 없지만 더 쉬운 공식적인 접근 방식이있을 수 있다고 생각합니다.

권장 사항을 만들기 위해서는 데이터에 대한 몇 가지 가정이 필요합니다. 예를 들어 모든 데이터가 다음 형식이라고 가정합니다.

My Town, CA 98765-4321

대시 이후의 부분 (우편 번호의 후행 부분)은 선택 사항이지만 쉼표의 위치는 정적이며 항상 도시 이름을 따르며주는 항상 두 문자로 구성됩니다. 이 경우 주 약어와 우편 번호의 처음 5 자리를 추출하는 두 가지 공식을 쉽게 고안 할 수 있습니다.

=MID(A1,FIND(",",A1)+2,2)

=MID(A1,FIND(",",A1)+5,5)

두 공식 모두 쉼표를 입력합니다. 도시와 실제로 원하는 두 항목 사이의 구분 기호 역할을합니다. 데이터에 쉼표가 없거나 여러 쉼표가있는 경우 수식이 원하는 정보를 반환하지 않습니다.

또한 수식은 데이터에 추가 공백이 없다고 가정합니다. 기껏해야 쉼표 뒤와 주와 우편 번호 사이에 하나의 공백이 있습니다. 물론 이것은 실행하기에 충분히 쉽습니다. 찾기 및 바꾸기를 사용하여 워크 시트의 어느 곳에서나 두 개의 공백을 단일 공백으로 바꾸십시오.

물론 데이터가 이렇게 구조화 된 경우에도 Text to Columns 도구를 사용하여 작업을 수행 할 수 있습니다. 도구를 실행하고 쉼표를 기준으로 데이터를 분할하기 만하면됩니다. 이것은 하나의 셀에 도시를 남겨두고 다음 셀에 주와 우편 번호를 함께 넣습니다. 그런 다음 텍스트를 열로 다시 사용할 수 있습니다. 이번에는 두 번째 셀 (도시 이름이 아님)에서 공백을 기준으로 내용을 나눌 수 있습니다.

데이터가 구조화되지 않은 경우 (예 : 주소에 여러 개의 쉼표가 있거나 추가 공백이있는 경우) 완전히 다른 접근 방식이 필요합니다. 이를 처리하기 위해 기본 기술은 데이터를 트리밍하여 불필요한 공간 (선행, 후행 및 내부)을 제거한 다음 마지막 공간과 마지막에서 두 번째 공간의 위치를 ​​결정하는 것입니다.

다음 공식을 사용하여 데이터의 마지막 공백 바로 뒤에 오는 것으로 정의되는 우편 번호의 5 자리 숫자를 추출 할 수 있습니다.

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

두 문자 상태 약어는 마지막에서 두 번째 공백 바로 뒤의 두 문자를 꺼내서 반환 할 수 있습니다.

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

데이터가 구조화되지 않은 경우 (예 : 모두 NJ 대신 N.J.) 두 문자로 된 상태 약어가없는 주소가 포함되어 있다면 매크로를 사용하여 데이터를 분할하는 것이 가장 좋습니다.

그 이유는 VBA에는 Excel 수식을 사용하여 수행 할 수있는 것보다 훨씬 더 풍부한 텍스트 처리 함수 집합이 있기 때문입니다. 다음 매크로는 주 또는 우편 번호를 반환 할 수있는 사용자 정의 함수를 만듭니다.

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function     arr = Split(rstrAddress, " ")

With arr         If UBound(arr) < 2 Then             sState = "?"

sZIP = "?"

Else             sState = arr(UBound(arr) - 1)

sZIP = arr(UBound(arr))

End If     End With     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

이 기능을 사용하려면 셀 참조와 1 (주를 원하는 경우) 또는 2 (우편 번호를 원하는 경우)를 제공하면됩니다. 다음은 A1 셀에있는 주소에 대해 우편 번호를 요청하는 예입니다.

=GetStateZIP(A1,2)

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (9599)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Extracting_a_State_and_a_ZIP_Code [주 및 우편 번호 추출].