지난 6편에서는 여러 사람이 공동으로 사용하는 시트에서 다른 사람에게 영향을 주지 않고 나만의 맞춤 조건으로 데이터를 조회하는 '필터 보기' 기능을 마스터했습니다. 이제 데이터를 선별해서 보는 눈을 갖추었으니, 이번 편에서는 외부에서 가져온 엉망진창인 데이터를 가공하는 방법에 대해 알아보겠습니다.
현업에서 업무를 하다 보면 외부 설문조사 결과나 시스템에서 추출한 회원 명부, 혹은 카카오톡이나 이메일로 받은 주소록을 스프레드시트에 그대로 복사해서 붙여넣어야 하는 상황이 자주 생깁니다. 그런데 큰 기대감을 안고 시트에 데이터를 붙여넣는 순간 깊은 한숨이 나오곤 합니다. 한 칸에 이름만 깔끔하게 들어가야 하는데, '홍길동 / 010-1234-5678 / 서울시 강남구'처럼 이름과 연락처, 주소가 한 셀에 쉼표나 슬래시로 뒤죽박죽 섞여서 통째로 입력되는 경우가 많기 때문입니다.
이 상태로는 지난번에 배웠던 SUM이나 VLOOKUP 같은 함수를 전혀 쓸 수 없습니다. 데이터를 기준으로 통계를 내거나 정렬을 하려면 이름은 이름 칸에, 연락처는 연락처 칸에 따로따로 분리가 되어 있어야 합니다. 수십, 수백 개의 셀을 보며 일일이 마우스로 드래그해서 지우고 다시 타이핑하는 노가다를 시작하려는 분들이 있다면 잠시 키보드에서 손을 떼셔도 좋습니다. 구글 스프레드시트의 '텍스트 나누기' 기능 하나면 단 5초 만에 이 복잡한 데이터를 칼로 자른 듯 깔끔하게 분리할 수 있습니다.
1. 데이터 분리의 핵심 원리: '구분 기호' 찾아내기
컴퓨터는 사람처럼 문맥을 읽고 "여기서부터는 주소니까 다른 칸에 넣어야지"라고 스스로 판단하지 못합니다. 대신 텍스트 사이에 들어가 있는 특정 규칙을 기반으로 데이터를 쪼갭니다. 이 규칙을 스프레드시트에서는 '구분 기호(Delimiter)'라고 부릅니다.
엉망으로 섞여 있는 데이터들을 유심히 살펴보면 일정한 패턴이 있습니다. 단어와 단어 사이에 공백(스페이스바)이 들어가 있거나, 쉼표(,), 슬래시(/), 세미콜론(;) 등이 일관되게 텍스트를 이어주고 있을 것입니다. 텍스트 나누기 기능은 바로 이 구분 기호를 기준으로 셀을 쪼개 우측으로 데이터를 나열하는 원리입니다.
2. 5초 만에 끝내는 마우스 클릭 기반 '텍스트 나누기'
가장 직관적이고 빠르게 텍스트를 분리하는 방법은 메뉴에 내장된 기본 기능을 사용하는 것입니다.
뒤죽박죽 섞인 데이터가 들어있는 열(예: A열)을 마우스로 드래그하여 선택합니다.
상단 메뉴에서 [데이터] -> [텍스트를 열로 분할]을 클릭합니다.
선택한 데이터 옆에 조그만 '구분 기호' 선택 팝업창이 나타납니다.
데이터가 쉼표로 연결되어 있다면 '쉼표', 공백으로 띄워져 있다면 '공백'을 선택합니다. 만약 슬래시(/)나 하이픈(-) 같은 특수 기호라면 맨 아래 '맞춤'을 선택한 뒤 해당 기호를 직접 입력합니다.
선택과 동시에 텍스트가 우측 열로 나란히 쪼개지며 분리되는 모습을 확인할 수 있습니다.
여기서 아주 중요한 주의사항이 있습니다. 텍스트 나누기를 실행하면 기존 셀의 우측에 있던 빈 칸들이 분리된 데이터로 채워집니다. 만약 우측 열에 이미 다른 중요한 데이터가 입력되어 있었다면, 그 내용 위에 분리된 데이터가 덮어써지면서 기존 데이터가 영구적으로 지워지게 됩니다. 따라서 이 기능을 쓰기 전에는 반드시 우측에 데이터를 받아줄 만한 빈 열을 충분히 삽입해 두는 습관을 들여야 내 소중한 자산을 지킬 수 있습니다.
3. 실무의 복병: 규칙이 제각각인 데이터 대응법
항상 원본 데이터가 아릅답게 통일되어 있지는 않습니다. 어떤 행은 '홍길동 / 서울', 어떤 행은 '이영희, 부산', 또 어떤 행은 구분 기호 없이 스페이스바 한 칸으로만 '김철수 대구'라고 적혀 있는 등 기호가 뒤섞여 있는 경우가 실무에서는 허다합니다. 이 상태에서 무작정 텍스트 나누기를 실행하면 어떤 것은 쪼개지고 어떤 것은 쪼개지지 않아 데이터가 수평이 맞지 않고 엉망이 됩니다.
[해결책: 1차 정제 후 분할하거나 수식을 사용하세요] 이런 복병을 만났을 때는 한 번에 나누려고 하면 안 됩니다. 가장 먼저 단축키(Ctrl + H)를 활용해 '찾아바꾸기' 기능을 켜야 합니다. 슬래시(/)나 공백을 전부 쉼표(,)로 통일하여 치환해 주는 1차 정제 작업을 거친 뒤 텍스트 나누기를 실행하면 실패 확률이 제로에 가까워집니다.
만약 원본 데이터의 형태를 그대로 유지하면서 다른 셀에 동적으로 분리된 값을 출력하고 싶다면 SPLIT 함수를 쓰는 것도 훌륭한 대안입니다. 빈 셀에 =SPLIT(A2, "/")라고 입력하면, A2 셀의 내용을 슬래시 기준으로 쪼개어 우측 셀들에 자동으로 펼쳐줍니다. 함수를 쓰면 원본이 훼손되지 않고 데이터가 추가될 때마다 실시간으로 분리되므로 자동화 관점에서 매우 유리합니다.
핵심 요약
한 셀에 뭉쳐 있는 데이터는 텍스트 사이에 들어간 쉼표, 공백, 슬래시 등의 '구분 기호'를 기준으로 분리할 수 있습니다.
[데이터] -> [텍스트를 열로 분할] 기능을 사용하면 단 몇 번의 클릭만으로 대량의 텍스트를 칸별로 쪼갤 수 있습니다.
기능 실행 시 우측 열에 있던 기존 데이터가 덮어써져 지워질 수 있으므로, 반드시 사전에 빈 열을 충분히 확보해야 합니다.
구분 기호가 제각각일 때는 '찾아바꾸기'로 기호를 통일한 후 나누거나,
SPLIT함수를 활용해 안전하게 동적 분리를 수행합니다.
다음 편 예고
데이터를 열별로 깔끔하게 쪼개 놓았는데, 가끔 수식을 복사해서 붙여넣다 보면 계산 결과가 한 칸씩 밀리거나 엉뚱한 값을 참조하여 수식이 깨지는 현상이 발생합니다. 다음 편에서는 스프레드시트 수식 오류의 가장 큰 원인이자 필수 개념인 "수식이 깨지는 주된 원인 '절대참조($)' 기호 100% 이해하기"에 대해 상세히 다루어 보겠습니다.
댓글 유도 질문
외부에서 데이터를 가져왔을 때 여러분을 가장 당황하게 만들었던 데이터의 형태(예: 줄바꿈이 들어간 셀, 기호가 무작위인 주소 등)는 무엇이었나요? 댓글로 남겨주시면 가장 손쉬운 정제 팁을 공유해 드리겠습니다!
0 댓글