💡 핵심 요약: 사내 시스템이나 외부 ERP에서 내려받은 원시 데이터(Raw Data)는 텍스트가 쉼표(,), 슬래시(/), 띄어쓰기로 뭉쳐 있어 바로 집계하기 어렵습니다. 본 글에서는 구형 방식인 [텍스트 마법사]를 쓰지 않고도, 단 하나의 수식으로 문자열을 자유자재로 쪼개고 합치는 실무 핵심 텍스트 함수 5가지를 정리합니다.
실무에서 엑셀 작업을 할 때 가장 많은 시간을 잡아먹는 일 중 하나는 바로 “지저분한 텍스트 데이터 정제”입니다. 예를 들어 ‘홍길동/마케팅팀/과장’처럼 한 셀에 합쳐진 정보를 열별로 나누거나, 여러 행에 흩어진 고객 요청 사항이나 품목명을 하나의 셀로 깔끔하게 묶어야 하는 상황이 빈번합니다.
과거에는 메뉴의 [데이터] > [텍스트 나누기]를 일일이 클릭하거나 복잡한 LEFT, FIND, MID 중첩 수식을 썼지만, 최신 엑셀에서는 TEXTSPLIT과 TEXTJOIN 함수만 알면 수 초 만에 자동화할 수 있습니다.
1. TEXTSPLIT: 특정 구분자 기준으로 텍스트 자동 분리
TEXTSPLIT은 셀 하나에 입력된 긴 문자열을 지정한 구분 기호(쉼표, 슬래시, 공백 등)를 기준으로 **옆쪽 열(또는 아래쪽 행)로 자동 펼쳐주는(동적 배열)** 혁신적인 함수입니다.
| 원본 셀 (A2) | 적용 수식 | 결과 (B2, C2, D2 자동 분리) |
|---|---|---|
| 서울시/강남구/역삼동 | =TEXTSPLIT(A2, “/”) | [서울시] [강남구] [역삼동] |
| 사과, 배, 포도, 수박 | =TEXTSPLIT(A3, “, “) | [사과] [배] [포도] [수박] |
2. TEXTJOIN: 빈 셀은 건너뛰고 깔끔하게 하나로 병합
기존의 & 연산자나 CONCATENATE 함수는 중간에 빈 셀이 있으면 ‘사과,,포도’처럼 불필요한 구분자가 연속으로 붙는 문제가 있었습니다. TEXTJOIN은 빈 셀을 자동으로 제외(무시)하고 원하는 구분자로 한 번에 이어 붙여줍니다.
👉 두 번째 인수에 TRUE를 넣으면 빈 셀을 완전히 무시하고 건너뜁니다.
3. 실무 텍스트 정제 필수 보조 함수 3선
단어 앞뒤의 쓸데없는 공백을 지우고 단어 사이의 연속된 공백을 1칸으로 정돈합니다. VLOOKUP 조회 시 공백 때문에 #N/A가 뜰 때 필수입니다.
=TRIM(A2)
사업자번호나 전화번호에서 하이픈(-)을 지우거나 특정 단어를 다른 단어로 변경할 때 사용합니다.
=SUBSTITUTE(A2, "-", "") /* 하이픈 전체 삭제 */
이메일 주소에서 아이디만 뽑거나(골뱅이 앞), 도메인만 뽑을 때(골뱅이 뒤) LEFT/RIGHT 없이 한 번에 처리합니다.
=TEXTBEFORE("user@company.com", "@") → "user" 반환
4. 실무 응용: 조건에 맞는 항목들만 한 셀로 묶기 (TEXTJOIN + IF)
예를 들어 거래처별로 주문한 여러 개의 품목명을 한 셀에 ‘품목A, 품목B, 품목C’처럼 콤마로 묶어서 요약 보고서를 만들 때 쓰는 실무 배열 수식입니다.
👉 A열이 ‘거래처A’인 행의 B열 품목명만 모아서 쉼표로 연결하고, 일치하지 않는 빈 값은 TRUE 옵션으로 자동 제외합니다.
데이터 조회 필수 함수인 XLOOKUP과 복합 검색 INDEX MATCH 활용법도 확인해 보세요.