Excel을 자주 사용하다 보면 하나의 셀에 저장된 이름을 여러 개의 셀로 나눠야 하는 상황을 자주 마주하게 됩니다. Excel에서 매우 흔한 문제라서 인터넷 검색만 하면 다양한 사람들이 만든 수백 가지 매크로를 내려받아 사용할 수 있습니다.
하지만 이 글에서는 남이 만든 매크로를 가져다 쓰는 대신, 직접 수식을 작성해서 원리를 제대로 이해하며 문제를 해결하는 방법을 소개합니다. Excel을 업무에 자주 활용한다면 고급 함수 몇 가지를 익혀두면 데이터를 훨씬 더 유연하고 재미있게 다룰 수 있습니다.
수식 입력이 번거롭고 빠른 해결책을 원한다면 아래쪽의 텍스트 나누기(Text to Columns) 섹션으로 바로 건너뛰셔도 좋습니다. 참고로 한 셀에 두 개 이상의 항목이 들어 있는 경우에는 텍스트 나누기 기능이 훨씬 유리합니다. 예를 들어 한 열에 6개의 필드가 합쳐져 있다면, 아래의 수식으로 처리할 때 작업이 지나치게 복잡해질 수 있습니다.
수식으로 이름 분리하기
먼저 Excel 시트에서 이름이 일반적으로 어떤 형태로 저장되는지 살펴보겠습니다. 가장 흔한 두 가지 형식은 공백 하나로 구분된 이름 성(firstname lastname)과 쉼표로 구분된 성, 이름(lastname, firstname)입니다. 중간 이름 이니셜(middle initial)이 포함되는 경우에는 대개 이름 이니셜 성(firstname midinitial lastname) 형태입니다.
간단한 수식 몇 개를 조합하면 이름, 중간 이니셜, 성을 각각 별도의 셀로 손쉽게 분리할 수 있습니다. 먼저 이름의 첫 번째 부분을 추출하는 방법부터 시작해 보겠습니다. 여기서는 LEFT 함수와 SEARCH 함수 두 가지를 사용합니다. 논리적인 처리 순서는 다음과 같습니다.
셀 안의 텍스트에서 공백 또는 쉼표를 찾아 그 위치를 확인한 뒤, 해당 위치보다 왼쪽에 있는 모든 문자를 가져옵니다.
이 작업을 정확히 수행하는 간단한 수식은 =LEFT(NN, SEARCH(" ", NN) - 1) 입니다. 여기서 NN은 이름이 저장된 셀을 의미합니다. 끝에 붙는 -1은 문자열 끝의 불필요한 공백이나 쉼표를 제거하기 위한 것입니다.
LEFT 함수는 두 개의 인수를 받습니다. 첫 번째는 대상 문자열이고, 두 번째는 문자열 시작 부분부터 가져올 문자 수입니다. 첫 번째 사례에서는 큰따옴표 안에 공백을 넣어 공백을 검색했고, 두 번째 사례에서는 공백 대신 쉼표를 검색했습니다. 앞서 언급한 세 가지 시나리오의 결과는 어떻게 될까요?
3행에서는 이름(첫 번째 부분)을, 5행에서는 성을, 7행에서는 다시 이름을 얻었습니다. 데이터가 어떤 형식으로 저장되어 있느냐에 따라 이름 또는 성 중 원하는 값을 추출할 수 있습니다. 이제 두 번째 부분을 추출해 보겠습니다. 논리적인 순서는 다음과 같습니다.
셀 안의 텍스트에서 공백 또는 쉼표의 위치를 찾은 뒤, 전체 문자열 길이에서 그 위치를 뺍니다. 수식은 다음과 같습니다.
=RIGHT(NN, LEN(NN) - SEARCH(" ", NN))
이번에는 RIGHT 함수를 사용합니다. RIGHT 함수 역시 두 개의 인수를 받는데, 첫 번째는 문자열이고 두 번째는 문자열 끝에서 왼쪽 방향으로 가져올 문자 수입니다. 즉, 전체 문자열 길이에서 공백이나 쉼표의 위치를 빼면, 첫 번째 공백 또는 쉼표 오른쪽에 있는 모든 문자를 얻을 수 있습니다.
이제 이름의 두 번째 부분까지 분리했습니다! 앞의 두 가지 경우라면 여기서 작업이 끝납니다. 하지만 중간 이니셜이 포함된 이름이라면 결과에 이니셜과 성이 함께 붙어 나오는 것을 볼 수 있습니다. 그렇다면 이니셜을 제거하고 성만 깔끔하게 추출하려면 어떻게 해야 할까요? 의외로 간단합니다. 두 번째 부분을 구할 때 사용했던 바로 그 수식을 한 번 더 실행하면 됩니다.
즉, 이니셜과 성이 합쳐진 셀에 동일한 RIGHT 수식을 적용하는 것입니다. 이렇게 하면 이니셜 뒤의 공백 위치를 찾아, 전체 길이에서 그 위치를 뺀 만큼의 문자를 문자열 끝에서 가져오게 됩니다.
완성되었습니다! 간단한 수식 몇 개만으로 이름과 성을 서로 다른 열로 분리하는 데 성공했습니다. 물론 모든 사람의 데이터가 위와 같은 형식으로 되어 있지는 않겠지만, 수식을 조금만 수정하면 자신의 데이터 형식에 맞게 쉽게 변경할 수 있습니다.
텍스트 나누기(Text to Columns) 기능 활용하기
Excel에서 결합된 텍스트를 여러 열로 나누는 또 다른 쉬운 방법이 있습니다. 바로 텍스트 나누기(Text to Columns) 기능인데, 매우 잘 작동하며 특히 한 열에 두 개 이상의 데이터가 들어 있는 경우 훨씬 효율적입니다.
예를 들어 아래 데이터에서 한 행은 4개의 데이터를, 다른 행은 5개의 데이터를 담고 있다고 가정해 보겠습니다. 이를 각각 4개 열과 5개 열로 나누려면, 앞서 소개한 수식을 사용하는 것은 비현실적입니다.
먼저 나누고자 하는 열을 선택한 후, 리본 메뉴에서 데이터(Data) 탭을 클릭하고 텍스트 나누기(Text to Columns)를 클릭합니다.
그러면 텍스트 나누기 마법사가 실행됩니다. 1단계에서는 필드가 구분 기호로 나뉘어 있는지(delimited), 아니면 고정 폭(fixed width)인지 선택합니다. 여기서는 구분 기호로 분리됨(Delimited)을 선택합니다.
다음 화면에서는 구분 기호를 지정합니다. 탭, 세미콜론, 쉼표, 공백 중에서 선택하거나 직접 사용자 지정 구분 기호를 입력할 수도 있습니다.
마지막으로 열의 데이터 형식을 선택합니다. 대부분의 데이터는 일반(General) 형식이면 충분하지만, 날짜처럼 특정 형식이 필요한 데이터라면 해당 형식을 선택하세요.
마침(Finish)을 클릭하면 데이터가 마법처럼 여러 열로 나뉘는 것을 확인할 수 있습니다. 한 행은 5개 열로, 다른 행은 4개 열로 분리되었습니다. 텍스트 나누기 기능은 매우 강력해서 반복적인 데이터 정리 작업을 크게 줄여줍니다.
위에서 소개한 형식과 다른 형태의 이름을 분리하는 데 어려움이 있다면, 댓글로 데이터 예시를 남겨주세요. 함께 해결 방법을 찾아보겠습니다!