엑셀 TEXTSPLIT · TEXTBEFORE · TEXTAFTER 완벽 가이드
반복적인 문자열 분해를 자동화하려면 엑셀 TEXTSPLIT이 가장 빠릅니다. CSV, “이름(부서)-이메일” 같은 복합 문자열을 손으로 자르지 말고 TEXTBEFORE/TEXTAFTER와 조합해 1초 컷으로 정규화하세요.
Quick Fix(3분 절차)
- 구분자 종류 파악(예:
,,-,|, 공백/괄호). =TEXTSPLIT(A2, {",","-"," | "})처럼 시작.ignore_empty=TRUE로 연속 구분자 빈 요소 제거.row_delimiter/col_delimiter로 세로/가로 제어.TRIM/CLEAN/SUBSTITUTE로 후처리,HSTACK/VSTACK으로 재배치.- #SPILL! 발생 시 주변 공간 비우고 병합 해제.
핵심 개념
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
- 여러 구분자는 배열 상수
{",","-"," ("}형태로 지정. ignore_empty로 빈 요소 제어,match_mode로 대소문자/와일드카드.pad_with로 누락 값 채우기.
TEXTBEFORE/TEXTAFTER 요점
- TEXTBEFORE:
instance_num로 N번째 구분자 선택,match_end=1이면 끝을 구분자로 취급. - TEXTAFTER: 동일 구조로 뒤쪽 텍스트 반환.
실무 예제
예제1: 이름/부서/이메일 분해
B2: =TEXTBEFORE(A2, "(")
C2: =TEXTBEFORE(TEXTAFTER(A2, "("), ")")
D2: =TEXTAFTER(A2, "-")예제2: TEXTSPLIT 한 방
=LET(s,A2, TXT, TEXTSPLIT(s, {"-","(",")"}), HSTACK(INDEX(TXT,1), INDEX(TXT,2), INDEX(TXT,3)))예제3: 여러 구분자 혼합 + 빈 요소 제거
=TEXTSPLIT(A2, {"/","|",","}, , TRUE)예제4: 마지막 구분자 기준 파일명
=TEXTAFTER(A2, "\", -1)
=TEXTBEFORE(TEXTAFTER(A2,"\",-1),".")예제5: 세로로 떨어뜨리기
=TEXTSPLIT(A2, , ",")고급 패턴
=TEXTSPLIT(A2, {",","|"," - "}, , TRUE, 1) // 대소문자 무시
=TEXTSPLIT(A2, ",", , FALSE, 0, "N/A") // 빈 요소 유지 + 패딩
=LET(arr, TEXTSPLIT(A2, ","), VSTACK(TAKE(arr,,2), DROP(arr,,2)))대체 방법과 주의사항
- 텍스트 나누기(데이터 > 텍스트 나누기): 간단하지만 동적 아님.
- Power Query: 대량·복합 처리 강력, 초기 세팅 필요.
- 구버전 대체: LEFT/RIGHT/MID/FIND/SUBSTITUTE 조합(유지보수 어려움).
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| #SPILL! | 결과 공간 막힘/병합 | 주변 비우기, 병합 해제, 표 밖 사용 |
| 빈 값 섞임 | 연속 구분자 | ignore_empty=TRUE, 후처리 FILTER |
| 매칭 실패 | 정확 일치 기본값 | match_mode=1/-1로 조정 |
| 열/행 뒤바뀜 | row/col 혼동 | 인수 순서 점검 |
| 이상 공백 | CHAR(160) 등 | SUBSTITUTE → TRIM/CLEAN |
맺음말 & 관련 글
이제 TEXTSPLIT 계열로 문자열을 즉시 구조화할 수 있습니다. 조건 집계·기간 처리까지 자동화하려면 아래 글을 참고하세요.