한 열의 데이터를 두 열로 분할

이전에 데이터 탭의 텍스트 나누기와 파워 쿼리의 열 분할에 대해 다뤄봤는데,
이번에는 다른 경우의 데이터 분할에 대해 알아보겠습니다.
 
통계청에서 조회한 행정구역(시군구)별  주민등록세대수 자료인데,

 
다운로드하여 보니 서울특별시와 종로구 등의 데이터가 열을 달리해야 데이터 다루기가 편한데 같은 열에 있습니다.

 

행정구역_시군구_별_주민등록세대수_20230804193213.xlsx

0.02MB

 
 
 
따라서, 이를 다른 열로 만드는 것에 대해 알아보겠습니다.
 

1. 구조 파악

서울특별시는 첫째 자리부터 시작하고, 종로구는 위치를 =FIND("종",A4) 수식으로 알아보니 4부터 시작합니다.

 

2. 분리하는 방법

 

가. 실패

1부터 시작하는 것과 4부터 시작하는 것을 두 개의 열에 나눠서 표시하면 됩니다.
F4셀의 수식은 복사할 때 방해가 되므로 F열과 G열을 선택한 후 마우스 오른쪽 버튼을 누른 후 삽입 메뉴를 눌러 오른쪽으로 이동시키겠습니다.
 
F1셀에 행정구역명(대), G1셀에 행정구역명(중)이라고 입력합니다.
 
그리고, F2셀에 =if(len(left(a2,1))>0,a2," ")라고 입력합니다.
left함수로 a2셀의 첫 번째 글자를 가져오고,
len함수로 글자의 길이를 구한 다음
if함수를 이용해 글자의 길이가 0보다 크면 a2셀의 값을 반환하고, 아니면 공백 한 칸(" ")을 반환하도록 하는 것입니다.
 
F2셀에 '전국'이라고 표시됩니다. 

 
오른쪽 정렬이 돼있는데, 왼쪽 정렬로 바꿉니다.

 
G2셀에 =if(len(left(a2,1))=0,mid(a2,4,10)," ")이라고 입력합니다.
왼쪽 첫 글자의 글자가 0이면 
mid함수를 이용해 a2셀의 4번째부터 글자 10개를 가져오도록 하고(10은 넉넉하게 글자의 길이를 입력한 것임),
아니면 공백 한 칸을 반환하도록 합니다.
 
A2셀의 글자가 1부터 시작하니 False가 돼서 공백 한 칸이 입력됐습니다.

 
이제 F2셀을 마우스로 클릭하고 G2셀까지 끈 후 채우기 핸들을 더블 클릭하면

 
맨 아래까지 수식이 모두 복사되는데, A열의 데이터와 동일하고, G열에 데이터가 하나도 없습니다.

 
 

나. 수식 수정

원인을 생각해 보니,
A4셀의 첫 번째 글자가 보이지는 않지만 공백이라 이것도 글자 한 자로 계산해서 그렇습니다.
따라서, 공백을 제거하는 함수인 Trim을 사용해서 수식을 수정해야 합니다.
 
F1셀의 수식을 수정하는데,
왼쪽 하나를 가져온 다음 글자를 잘라야(Trim) 하므로 
Left앞에 Trim(를 입력하고 Left함수가 끝나는 부분에 괄호가 2개 있는데 여기에 )를 하나 더 추가합니다.
따라서, 수식은 =IF(LEN(TRIM(LEFT(A2,1)))>0,A2," ")이 됩니다.

 
마찬가지로 G2셀의 수식도
=IF(LEN(TRIM(LEFT(A2,1)))=0,MID(A2,4,10)," ")이 됩니다.

 
다시 F2셀과 G2셀을 선택한 다음 채우기 핸들을 클릭하면 원하는 대로, 서울특별시와 종로구 등이 분리됐습니다.

행정구역명 분리(완성).xlsx

0.03MB