한 셀에 다 들어있는 데이터, 실무에서 자주 만나는 문제
관공서나 중소기업 실무를 하다 보면 이런 엑셀 파일을 자주 받습니다.
- A열에 “홍길동/010-1234-5678/경기도 양주시”처럼 이름, 연락처, 주소가 구분자 하나로 뭉쳐 있는 명단
- 반대로 성과 이름, 부서명과 직급이 따로따로 흩어져 있어서 하나로 합쳐야 보고서 양식에 맞는 경우
이런 파일을 받으면 손으로 하나씩 잘라내거나 복사해 붙이는 분들이 많은데, 데이터가 수백 건만 넘어가도 이 방식은 시간도 오래 걸리고 실수도 잦습니다. 엑셀에는 이런 상황을 위한 기능과 함수가 이미 준비되어 있습니다. 이번 글에서는 텍스트를 나누는 방법과 합치는 방법을 실무 예시로 정리해보겠습니다.
텍스트 나누기: 마법사부터 함수까지
1) 텍스트 나누기 마법사 (구분 기호가 일정할 때)
가장 먼저 떠올릴 수 있는 건 [데이터] 탭의 “텍스트 나누기” 기능입니다. A열에 “홍길동/010-1234-5678/경기도 양주시” 형태로 슬래시(/)가 일정하게 들어가 있다면 다음 순서로 처리합니다.
- 나눌 데이터가 있는 열을 선택합니다.
- [데이터] → [텍스트 나누기]를 클릭합니다.
- “구분 기호로 분리됨”을 선택하고 다음으로 넘어갑니다.
- 구분 기호에서 “기타”를 체크하고 슬래시(/)를 입력합니다.
- 미리 보기에서 열이 제대로 나뉘는지 확인한 뒤 마침을 누릅니다.
이 방법은 빠르고 직관적이지만, 원본 데이터를 그대로 덮어쓴다는 단점이 있습니다. 원본을 보존하고 싶다면 결과가 들어갈 위치를 다른 열로 지정하거나, 작업 전에 원본을 복사해두는 습관이 필요합니다.
2) TEXTSPLIT 함수 (엑셀 365, 결과가 자동으로 갱신됨)
마이크로소프트 365 버전을 쓰고 계시다면 TEXTSPLIT 함수를 추천합니다. 마법사와 달리 원본 셀을 건드리지 않고, 원본 데이터가 바뀌면 결과도 자동으로 갱신됩니다.
=TEXTSPLIT(A2, "/")A2 셀의 “홍길동/010-1234-5678/경기도 양주시”를 슬래시 기준으로 나눠 B2, C2, D2에 자동으로 뿌려줍니다. 세로로 나누고 싶다면 세 번째 인수를 활용합니다.
=TEXTSPLIT(A2, ",", ";")이 경우 쉼표는 가로(열) 구분 기호, 세미콜론은 세로(행) 구분 기호로 동작해서, 한 셀 안에 콤마와 세미콜론이 섞여 있는 복잡한 데이터도 한 번에 표로 펼칠 수 있습니다.
3) LEFT, MID, FIND 조합 (구형 버전에서도 가능)
TEXTSPLIT을 쓸 수 없는 구형 버전이라면 문자열 함수 조합으로 비슷한 효과를 낼 수 있습니다. 예를 들어 ”-” 앞의 문자만 추출하고 싶다면 다음과 같이 씁니다.
=LEFT(A2, FIND("-", A2) - 1)FIND 함수로 구분 기호의 위치를 찾고, LEFT로 그 앞부분만 잘라내는 방식입니다. 반대로 구분 기호 뒤쪽을 가져오려면 MID와 LEN을 조합합니다.
=MID(A2, FIND("-", A2) + 1, LEN(A2))수식이 조금 복잡해 보이지만, 한 번 만들어두면 자동 채우기로 아래 행까지 그대로 적용할 수 있어 반복 작업에는 충분히 실용적입니다.
텍스트 합치기: TEXTJOIN과 & 연산자
1) & 연산자로 간단히 합치기
성과 이름이 따로 떨어져 있는 경우, 가장 단순한 방법은 & 연산자입니다.
=B2&C2성과 이름 사이에 공백을 넣고 싶다면 다음처럼 작성합니다.
=B2&" "&C22) TEXTJOIN으로 여러 셀을 한 번에
합칠 셀이 서너 개를 넘어가면 & 연산자를 계속 이어 쓰기가 번거로워집니다. 이때는 TEXTJOIN 함수가 훨씬 편합니다.
=TEXTJOIN(", ", TRUE, B2:E2)첫 번째 인수는 구분 기호, 두 번째 인수는 빈 셀을 무시할지 여부(TRUE면 무시), 세 번째 인수는 합칠 범위입니다. 부서명, 직급, 이름, 연락처가 각각 다른 열에 있을 때 이 함수 하나로 “기획팀, 대리, 홍길동, 010-1234-5678” 같은 문자열을 한 번에 만들 수 있습니다.
특히 여러 행의 데이터를 콤마로 구분된 하나의 문자열로 만들어야 하는 보고서 작업에서 TEXTJOIN은 활용도가 높습니다. 예를 들어 부서별 참석자 명단을 한 셀에 정리해야 할 때, 필터로 부서를 걸러낸 뒤 TEXTJOIN으로 묶으면 수작업으로 복사해 붙이는 것보다 훨씬 빠르고 실수도 줄어듭니다.
실무 팁: 나누기 전에 데이터 상태부터 확인하세요
텍스트를 나누거나 합치는 작업에서 실제로 시간을 가장 많이 잡아먹는 부분은 함수 자체가 아니라 데이터의 불규칙성입니다. 다음 사항을 미리 점검하면 작업이 훨씬 수월해집니다.
- 구분 기호가 일정한지 확인합니다. 어떤 행은 슬래시, 어떤 행은 하이픈을 쓰는 경우 함수 하나로 처리되지 않습니다.
- 앞뒤 공백이 섞여 있는지 확인합니다. TRIM 함수로 미리 정리해두면 나누기 결과가 훨씬 깔끔해집니다.
- 셀 안에 줄바꿈이 포함된 경우, 일반적인 구분 기호 검색으로는 잡히지 않으니 CHAR(10)을 구분 기호로 지정해야 합니다.
이런 불규칙한 데이터를 정리할 때 특정 패턴을 규칙으로 찾아내는 방법도 함께 알아두면 좋습니다. 구분 기호가 일정하지 않거나 데이터 형식이 뒤섞여 있는 경우에는 정규표현식을 활용하면 훨씬 유연하게 텍스트를 다룰 수 있습니다.
또한 이렇게 나누고 합친 데이터를 명단으로 활용해 대량 문서를 만들어야 하는 상황이라면, 메일 병합 기능으로 이어서 처리하는 것도 실무에서 자주 쓰이는 흐름입니다.
함수만으로 한계를 느낀다면
TEXTSPLIT과 TEXTJOIN은 대부분의 상황을 커버하지만, 파일이 수십 개로 나뉘어 있거나 나누는 규칙이 파일마다 다르다면 엑셀 함수만으로는 한계가 옵니다. 이런 경우에는 파이썬의 pandas 라이브러리로 여러 파일을 한 번에 불러와 정리하는 방식이 더 효율적입니다. 반복되는 텍스트 정리 업무가 매달 발생한다면 엑셀 자동화 작업으로 전환하는 것도 검토해볼 만합니다.
정리하며
엑셀 셀 안의 텍스트를 나누고 합치는 작업은 사소해 보이지만, 명단 관리나 보고서 작성 과정에서 매우 자주 발생하는 실무입니다. 텍스트 나누기 마법사는 빠르지만 원본을 덮어쓰는 단점이 있고, TEXTSPLIT과 TEXTJOIN은 최신 버전에서 원본을 보존하면서 자동으로 갱신되는 장점이 있습니다. 상황에 맞는 방법을 선택해 쓰시면 반복되는 수작업을 상당 부분 줄일 수 있습니다.
만약 사내에서 반복되는 데이터 정리 업무가 너무 많아 엑셀 함수만으로는 감당이 안 되거나, 여러 시스템에서 내려받은 데이터를 매번 손으로 가공하고 계시다면 픽셀앤코드의 데이터 가공 및 업무 자동화 서비스를 통해 작업 흐름을 점검받아보시는 것도 방법입니다.