TEXTSPLIT · TEXTBEFORE · TEXTAFTER로 텍스트 분리/추출 끝! (정규식 없이 1분 컷)
CSV, 파일 경로, 제품코드, 이메일… 이 3형제면 정규식 없이도 깔끔 분리/추출이 가능합니다. 자주 쓰는 패턴을 모아 바로 복붙할 수 있게 정리했어요.
핵심 개념 & 문법
| 함수 | 형식 | 설명 |
|---|---|---|
| TEXTSPLIT | =TEXTSPLIT(text, col_delim, [row_delim], [ignore_empty], [match_mode], [pad_with]) | 문자열을 배열로 분리 |
| TEXTBEFORE | =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | 구분자 이전 텍스트 |
| TEXTAFTER | =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | 구분자 이후 텍스트 |
TEXTSPLIT 베이직: 구분자 분리
예제 1) 하이픈(-)으로 열에 분리
=TEXTSPLIT(A2,"-")예제 2) 쉼표(,) 연속 공백 포함 CSV
=TEXTSPLIT(A2,", ") // ", "를 하나의 구분자로 인식예제 3) 줄바꿈(행) 기준 세로 분리
=TEXTSPLIT(A2,,CHAR(10)) // col_delim 생략, row_delim=줄바꿈연속 구분자·대소문자·여러 구분자
예제 4) 연속 구분자 빈값 무시
=TEXTSPLIT(A2,",",,TRUE)예제 5) 여러 구분자 동시 인식
=TEXTSPLIT(A2,{",",";","|"})예제 6) 대소문자 무시(match_mode=1)
=TEXTSPLIT(A2,"abc",,FALSE,1)TEXTBEFORE/AFTER: N번째·마지막
예제 7) 이메일 도메인 (첫 번째 ‘@’ 뒤)
=TEXTAFTER(A2,"@")예제 8) 파일 확장자 (마지막 ‘.’ 뒤)
=TEXTAFTER(A2,".",-1)예제 9) 경로의 상위 폴더 (마지막 ‘\’ 앞)
=TEXTBEFORE(A2,"\\",-1)예제 10) N번째 하이픈 앞/뒤
=TEXTBEFORE(A2,"-",N)
=TEXTAFTER(A2,"-",N)- if_not_found로 기본값 제공 가능. 간단히는
IFERROR(TEXTAFTER(...),"없음"). - match_end=1이면 문자열 끝도 구분자로 간주합니다.
행/열 변환 & N번째 토큰 추출
예제 11) 가로 분리 결과를 세로로
=TRANSPOSE(TEXTSPLIT(A2,"-"))예제 12) N번째 토큰만 뽑기
=INDEX(TEXTSPLIT(A2,"-"), N)예제 13) 최대 3개씩 행으로 감싸기
=WRAPROWS(TEXTSPLIT(A2,", "), 3, "")구버전 대체식(전통 공식)
TEXT 함수들이 없는 환경(구버전)에서는 아래 패턴을 사용하세요.
예제 14) 마지막 구분자 뒤
=RIGHT(A2, LEN(A2)-FIND("|", SUBSTITUTE(A2,"-","|", LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))))예제 15) 두 구분자 사이(중간 코드)
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)예제 16) 텍스트 나누기(리본)
데이터 → 텍스트 나누기로 일회성 분리, 또는 Power Query의 열 분할 기능 사용.
실수·예외·성능 체크리스트
- 연속 구분자 빈셀 발생 →
ignore_empty=TRUE. - 대소문자 민감 →
match_mode=1또는 대소문자 통일(UPPER/LOWER). - 가변 길이 행 →
pad_with로 공백 채우기, 또는WRAPROWS·WRAPCOLS로 그리드화. - 범위 일괄 처리 → BYROW/MAP로 TEXTSPLIT을 행 단위 적용.
- 성능 → 동일 계산은
LET으로 캐싱, 전체열(X:X) 참조 지양.
요약 정리
| 목표 | 대표 공식 |
|---|---|
| 전체 분리 | TEXTSPLIT(A2,"-") |
| N번째 앞/뒤 | TEXTBEFORE/AFTER(A2,"-",N) |
| 마지막 기준 | TEXTBEFORE/AFTER(A2,"-", -1) |
| 연속 구분자 무시 | TEXTSPLIT(A2,",",,TRUE) |
FAQ
여러 줄 텍스트를 행으로 자르려면?
=TEXTSPLIT(A2,,CHAR(10)) 또는 =TRANSPOSE(TEXTSPLIT(A2,"-"))처럼 TRANSPOSE를 활용하세요.
빈값/누락은 어떻게 처리하나요?
IFERROR(TEXTAFTER(...),"없음") 또는 TEXTSPLIT의 pad_with로 기본 채움값을 지정합니다.