텍스트 나누기부터 파워 쿼리까지: 데이터 분리 기술 총정리

방법 1: [텍스트 나누기] 기능 사용

실무 데이터를 받았는데 '김지우(A-101)'처럼 이름과 사번이 한 셀에 뭉쳐 있습니다. 이름과 사번을 구분해서 별도의 셀에 나누어 담아야 한다면 어떻게 해야 할까요? 이걸 일일이 복사해서 나누고 있었다면 엑셀의 [텍스트 나누기] 기능으로 손쉽게 해결할 수 있습니다.

(1) 텍스트 나누기를 적용할 영역을 범위로 지정하고 [데이터] 탭 > [데이터 도구] 그룹 > [텍스트 나누기]를 선택합니다.

(2) [텍스트 마법사] 대화상자에서 [구분 기호로 분리됨] 항목을 선택하고 [다음]을 클릭합니다.

(3) 이 경우 사번은 '(' 기호로 구분됩니다. 따라서 [기타] 항목에 '('를 입력하고 [마침]을 클릭합니다.

(4) '사번'이 별도의 열로 분리됩니다. 사번 뒤의 ')'는 Ctrl + H를 눌러[바꾸기] 기능을 사용하면 쉽게 제거할 수 있습니다([찾기 및 바꾸기] 대화상자에서 '찾을 내용'에 ')', 바꿀 내용에는 아무 것도 입력하지 않은 채 [모두 바꿈] 선택).

방법 2: [빠른 채우기] 기능 사용

엑셀의 [빠른 채우기] 기능을 사용하면 원하는 데이터를 빠르게 추출할 수 있습니다. '채우기'라는 명칭과 다르게 '추출'도 가능합니다.

(1) 첫 번째 사번이 들어갈 셀(여기서는 C2 셀)을 선택합니다.

(2) 첫 번째 사원의 사번인 A-101을 입력하고 엔터를 누릅니다.

(3) 아직까지는 아무 일도 일어나지 않으며, 셀 포인터는 C3 셀에 있습니다. 이 상태에서 [데이터] 탭 > [데이터 도구] 그룹 > [빠른 채우기]를 선택합니다.

단축키인 Ctrl + E를 눌러도 됩니다. [빠른 채우기]를 실행하면 엑셀은 사용자가 입력한 데이터에서 패턴을 찾아서 범위 내의 나머지 데이터에도 자동으로 적용해 줍니다.

방법 3: 엑셀 함수 사용

데이터를 여러 열로 분할하는 또 다른 방법은 Excel의 함수를 사용하는 것입니다. LEFT, RIGHT, MID 같은 문자열 함수를 사용하지만 엑셀 최근 버전을 사용한다면 TEXTSPLIT을 비롯한 업그레이드된 함수를 사용하여 간단하게 처리할 수 있습니다.

TEXTSPLIT 함수

Excel의 TEXTSPLIT은 지정한 구분 기호에 따라 셀의 모든 내용을 여러 열로 나누고, 결과를 분산된 배열로 반환합니다.

C2 셀에 다음 수식을 입력합니다. 데이터가 괄호로 구분되어 있어서 수식이 조금 복잡합니다.

=TEXTSPLIT(B2,{"(",")"},,TRUE)

채우기 핸들을 이용하여 수식을 나머지 영역에도 채워넣어 완성합니다.

TEXTBEFORE와 TEXTAFTER 함수

TEXTSPLIT이 셀의 모든 내용을 한꺼번에 나눠주는 반면, TEXTBEFORE와 TEXTAFTER 함수를 사용하면 구분 기호 앞이나 뒤에서 정보를 추출할지 선택할 수 있습니다. 이 함수들은 동적 배열 함수가 아니므로 표 형식으로 된 데이터에도 사용할 수 있습니다.

예를 들어, 엑셀 표 내부의 C2 셀에 다음 수식을 입력합니다.

=TEXTBEFORE([@[담당자(사번)]],"(")

여기서 '[@[담당자(사번)]]'는 분할하려는 셀이 포함된 열에 대한 구조화된 참조입니다.

괄호 ( ) 안에 있는 사번만 추출하려면 다음 수식을 사용합니다.

=TEXTBEFORE(TEXTAFTER([@[담당자(사번)]],"("),")")

TEXTAFTER로 'A-101)'을 먼저 추출한 후, 다시 그 값에서 TEXTBEFORE로 닫는 괄호 앞 부분만 골라내는 방식입니다. 함수 안에 함수를 넣어 한 번에 해결하는 중첩 함수의 위력입니다.

LEFT, MID, RIGHT 함수

LEFT, MID, RIGHT 함수를 사용하면 문자열 내 특정 위치에서 셀 내용을 분할할 수 있습니다. 이 함수들은 일련 번호나 코드처럼 구조적으로 일관된 값으로 구성된 데이터 집합이 있는 경우 특히 유용합니다.

데이터가 엑셀 표로 되어 있는 경우, 담당자를 추출하기 위한 수식은 다음과 같습니다.

=LEFT([@[담당자(사번)]], FIND("(", [@[담당자(사번)]])-1)

사번을 추출하는 수식입니다. MID와 FIND 함수를 조합하여 사용하였으며, 여는 괄호 다음 위치부터 시작해서, 닫는 괄호와 여는 괄호 사이의 글자 수만큼 데이터를 뽑아냅니다.

=MID([@[담당자(사번)]], FIND("(", [@[담당자(사번)]])+1, FIND(")", [@[담당자(사번)]])-FIND("(", [@[담당자(사번)]])-1)

방법 4: 파워 쿼리 사용

이런 방식의 데이터를 처리하는데 파워 쿼리를 빼놓을 수 없습니다. 많은 사람들이 Excel의 Power Query 편집기가 너무 복잡하다고 생각합니다. 그러나 이 편집기는 사용자 친화적으로 특별히 설계되었으며 데이터를 여러 열로 분할하는 좋은 방법입니다.

(1) 데이터 내부의 아무 셀이나 선택하고 [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [테이블/범위에서]를 선택합니다.

(2) 분할하려는 열 머리글(여기서는 '담당자' 열)을 우클릭하고 [열 분할] > [구분 기호 기준]을 선택합니다.

(3) 대화상자에서 [구분 기호 선택 또는 입력] 항목에 여는 괄호 "("를 입력하고 [확인]을 클릭합니다.

열 머리글을 더블클릭하여 이름을 바꿀 수 있습니다.

(4) 사번 오른쪽에 있는 닫는 괄호 ')'를 처리할 차례입니다. 해당 열 머리글을 우클릭하고 [값 바꾸기]를 선택합니다.

(5) 대화상자에서 '찾을 값'에 닫는 괄호 ')'를 입력하고, '바꿀 값'에 빈칸을 입력한 후 [확인]을 클릭합니다.

(6) [Power Query 편집기] 대화상자 왼쪽 상단의 [닫기 맟 로드] 아이콘의 상단 절반을 클릭합니다.

MVP TIP: 실무자를 위한 상황별 도구 선택 가이드

1. 어떤 방법을 선택해야 할까요?

단발성 작업이라면 [빠른 채우기(Ctrl+E)]가 가장 빠릅니다. 하지만 원본 데이터가 수시로 업데이트되거나 전처리 단계가 복잡하다면 고민하지 말고 파워 쿼리를 선택하세요. 엑셀 상단 [데이터] 탭 > [모두 새로 고침] 클릭 한 번이면, 여러분이 고생해서 설정한 분할과 정제 과정이 새 데이터에도 즉시 자동 적용됩니다.

2. 실수해도 괜찮습니다, 파워 쿼리가 있으니까요!

함수나 텍스트 나누기는 한 번 실수하면 'Ctrl+Z'로 되돌리거나 수식을 다시 써야 하죠? 하지만 파워 쿼리는 다릅니다. 우측 [적용된 단계] 목록에서 잘못된 단계 옆의 'X' 표시만 누르면 언제든 안전하게 전 단계로 돌아갈 수 있습니다. 파워 쿼리는 여러분의 실수를 완벽하게 관리해 주는 든든한 조력자입니다.

마치며

오늘 우리는 '김철수(A-101)'라는 하나의 데이터를 가지고 클래식한 방법부터 최신 함수, 그리고 파워 쿼리까지 총 4가지의 해결책을 살펴봤습니다. 중요한 것은 "어떤 함수를 외우느냐"가 아니라 "상황에 맞는 가장 효율적인 도구를 꺼낼 수 있느냐"입니다.

특히 파워 쿼리는 한 번만 제대로 구조를 잡아두면 평생의 퇴근 시간을 앞당겨주는 마법 같은 도구입니다. 오늘 배운 기능들을 여러분의 실무 파일에 직접 적용해 보세요. 지루한 반복 작업에서 벗어나 더 가치 있는 데이터 분석에 집중할 수 있는 시간이 열릴 것입니다. 지금 바로 여러분의 엑셀에서 파워 쿼리 편집기를 열어보시기 바랍니다!