Allan에는 수백 개의 이름과 주소 목록이 있습니다. 거리 주소의 범위는 Main Street, 123 Main Street, US RT 2 또는 187 South Elm St입니다. 주소에서 거리 번호를 구분하려고합니다.

따라서 주소 123 Main Street는 한 셀에서 “123”으로 끝나고 다른 셀에서 “Main Street”로 끝납니다. 번지가 없으면 번지 열에 아무것도 표시되지 않습니다. Text to Columns 도구는 작동하지 않으며 어떻게이 작업을 수행 할 수 있는지 궁금합니다.

완벽한 세상에서 Excel을 사용하면 거리 이름에서 숫자를 쉽게 분리 할 수 ​​있습니다. 이 옵션이 존재하지 않기 때문에 몇 가지 선택 사항이 있습니다. 가장 시간이 많이 걸리는 옵션은 추가 열을 추가하고 데이터를 다시 입력하는 것입니다. 그러나 시간을 절약하려면 다양한 공식을 사용하여 작업을 수행 할 수 있습니다.

주소 목록이 A 열 (A1 셀에서 시작)에 있다고 가정하면 다음과 유사한 수식을 사용하여 주소의 숫자 부분을 추출 할 수 있습니다.

=IF(ISERROR(VALUE(LEFT(A1,1))),"",LEFT(A1,FIND(" ",A1)-1))

B1 셀에 수식을 넣었다고 가정하면 다른 수식을 사용하여 주소의 숫자가 아닌 부분을 파생 할 수 있습니다.

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

이 방법에는 제한이 있습니다. 특히 주요 대도시 지역의 일부 주소는 152-33 Bell Blvd와 같은 형식을 사용합니다. 위의 공식은 이러한 주소에 대해 작동하지만 대안 인 152 33 Bell Blvd.를 사용하면 공식이 잘못 구문 분석됩니다. 전문적으로 개발 된 주소 구문 분석 프로그램을 구입하지 않으려면 위의 공식과 결과에 대한 빠른 안구 스캔이 적절해야합니다.

이 경우 다른 공식이 작동합니다. 주소가 A2 셀에 있다고 가정하고 다음 수식을 B2 셀에 입력합니다.

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

이 공식은 “첫 번째 문자가 숫자가 아니면 셀을 비워 두십시오. 그렇지 않으면 왼쪽에있는 모든 문자를 첫 번째 공백은 제외하고 제외하십시오.”라고 말합니다. 그런 다음이 수식의 결과를 사용하여 주소에서 숫자가 아닌 부분을 추출 할 수 있습니다.

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

또 다른 방법은 배열 수식을 사용하는 것입니다. 여기서도 주소가 A2 셀에 있다고 가정하면 다음을 사용할 수 있습니다.

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE,LEFT(A2,FIND(" ",A2,1)),"")

이것은 배열 수식이므로 Ctrl + Shift + Enter를 사용하여 입력해야합니다. 결과적으로 수식은 주소의 선행 숫자 부분을 반환합니다. 그런 다음 다음 배열 수식을 사용하여 숫자가 아닌 부분을 확인할 수 있습니다.

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

마지막으로 다음 매크로를 사용하여 거리 이름에서 주소를 구분할 수 있습니다.

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

이 매크로를 사용하려면 주소가 포함 된 셀 범위를 선택한 다음 실행하면됩니다. 주소의 선행 숫자 부분이 각 주소의 오른쪽에있는 셀에 나타나고 주소의 잔액이 그 오른쪽에있는 셀에 배치됩니다. (따라서 선택한 주소 오른쪽에 두 개의 빈 열이 있는지 확인해야합니다.)

_ 참고 : _

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

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

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

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

link : / excel-Extracting_Street_Numbers_from_an_Address [주소에서 번지 추출].