Tom은 많은 주소 목록을 사용합니다. 그를 미치게 만드는 한 가지는 거리 주소가 단일 셀에있을 때입니다. Tom은 한 셀의 집 번호와 다른 셀의 거리가 필요합니다. 따라서 주소가 “1234 Maple Glen Ave.”이면 한 셀에 “1234”와 “Maple Glen Ave”가 필요합니다. 또 다른. 그는 거리 이름 앞에있는 열에 집 번호를 복사 할 수 있지만 번호를 다시 입력 한 다음 거리 이름에서 제거하는 것은 긴 과정입니다. Tom은 거리 이름에서 집 번호를 “분할”하는 더 간단한 방법이 있는지 궁금합니다.

이 작업을 수행 할 수있는 몇 가지 방법이 있습니다. 어떤 사람들은 텍스트를 열로 도구 (리본의 데이터 탭에 있음)를 사용한다고 생각할 수 있습니다.

원하는 결과를 얻을 수 있습니다. 안타깝게도 집 번호를 구분하는 가장 좋은 방법은 아닙니다. 그 이유는 간단합니다. 구분 기호를 지정하여 도구를 사용하는 경우 의미있는 유일한 것은 공백입니다. 하지만 그렇게하면 “1234 Maple Glen Ave”가됩니다. 원하는 두 개 대신 네 개의 열에 걸쳐 있습니다. 그런 다음 거리 이름을 다시 붙여 넣는 방법을 찾아야합니다.

텍스트에서 단으로 도구를 잠재적으로 사용하는 다른 방법은 구분 기호 대신 고정 된 단 너비를 사용하는 것입니다. 이 접근 방식은 모든 주소의 집 번호에 동일한 자릿수가있는 경우에만 작동합니다. 물론 대부분의 데이터 세트에서는 그렇지 않습니다. 집 번호는 거의 모든 자릿수 일 수 있으며 숫자가 아닌 문자 (예 : “1234A”, “1234-B”또는 ‘1234-36’).

작동하는 한 가지 방법은 수식을 사용하여 원래 주소를 분리하는 것입니다. 수식은 주소의 첫 번째 공백에 입력하여 공백의 왼쪽 부분 또는 오른쪽 부분을 반환 할 수 있습니다. 다음은 A1 셀에 주소가 주어지면 공백 앞에있는 모든 것을 꺼내는 방법입니다.

=LEFT(A1,FIND(" ",A1)-1)

주소에 숫자가 아닌 문자가 포함되지 않는다고 확신하는 경우 VALUE 함수에서 수식을 래핑하여 숫자 값으로 집 번호로 끝낼 수 있습니다.

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

첫 번째 공백 뒤의 주소 부분을 가져 오려면 다음 공식을 사용할 수 있습니다.

=MID(A1,FIND(" ",A1)+1,LEN(A1))

원하는 경우 첫 번째 공식으로 뽑은 집 번호를 참조하는 공식을 고안 할 수 있습니다. 예를 들어, 집 번호 공식이 B 열에 있다고 가정합니다. C 열에 다음을 배치 할 수 있습니다.

=TRIM(SUBSTITUTE(A1,B1,))

B와 C 열의 공식 (및 A 열의 주소)을 사용하여 필요한만큼 많은 행에 대해 공식을 복사 할 수 있습니다. 그런 다음 해당 B : C 범위를 선택하고 선택하여 붙여 넣기를 사용하여 값을 해당 셀에 다시 붙여 넣을 수 있습니다. 이렇게하면 A 열의 원래 주소를 안전하게 삭제할 수 있습니다.

자주 많은 주소를 처리해야하는 경우 매크로를 사용하여 분리하는 것이 더 나을 수 있습니다. 다음 예제 매크로는 실행할 때 선택한 범위에서 작동합니다. 선택 항목의 왼쪽에 빈 셀 열을 삽입하고 해당 셀에 집 번호를 채운 다음 주소를 조정하여 집 번호가 더 이상 포함되지 않도록합니다.

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

원하는 경우 집 번호 만 반환하는 사용자 정의 함수를 만들 수 있습니다.

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

사용자 정의 함수 사용의 장점은 원래 주소의 첫 번째 부분이 실제로 숫자로 시작하는지 여부를 확인하는 것입니다. 그렇지 않은 경우 주소가 집 번호로 시작하지 않는다고 가정합니다. (전체 집 번호는 숫자 일 필요는 없으며 숫자로 시작하면됩니다.)

_ 참고 : _

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

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

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

이 팁 (13350)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.