TEXTSPLIT · TEXTBEFORE · TEXTAFTER로 문자열 분리·추출 끝! (구분자·정규패턴·N번째 1분 컷)
“이메일에서 도메인만”, “주소를 시·구·동으로”, “SKU-123-456에서 각 블록” — TEXTSPLIT/BEFORE/AFTER로 한 줄 수식으로 해결합니다.
문법 & 옵션
| 함수 | 형식 | 핵심 옵션 |
|---|---|---|
| TEXTBEFORE | =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | instance_num: N번째 전에 끊기, 음수는 뒤에서부터. match_end로 끝 인접 매칭 |
| TEXTAFTER | =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | instance_num: N번째 이후, match_mode=1 대소문자 무시 |
| TEXTSPLIT | =TEXTSPLIT(text, [col_delim], [row_delim], [ignore_empty], [match_mode], [pad_with]) | 다중 구분자 배열 지원, 누락 값은 pad_with로 채움 |
팁: 구분자가 여러 개면 {"-","/","_"}처럼 배열로 전달하고, 공백 문제는 먼저 TRIM으로 정리하세요.
TEXTBEFORE/AFTER — 앞/뒤 추출
이메일 아이디/도메인
=TEXTBEFORE(A2,"@")
=TEXTAFTER(A2,"@")파일 경로에서 파일명(마지막 \ 이후)
=TEXTAFTER(A2,"\",-1)SKU-123-456에서 가운데 블록
=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")TEXTSPLIT — 다중 구분자 분리
쉼표·세미콜론 혼용 연락처
=TEXTSPLIT(TRIM(A2), {",",";"," "},, TRUE)행/열로 동시에 분리(주소)
=TEXTSPLIT(A2, ", ", CHAR(10)) // 열: 콤마, 행: 줄바꿈누락 값은 0으로 패딩
=TEXTSPLIT(A2, ",",, , , 0)실무 패턴 12가지
① 검색어 쪼개서 AND 필터
=LET(k, TEXTSPLIT(LOWER($H$2)," "), FILTER(A2:A100, BYROW(A2:A100, LAMBDA(r, SUM(--ISNUMBER(SEARCH(k, LOWER(r))))=ROWS(k)))))② 성명 분리(영문 Last, First)
=TEXTSPLIT(A2, ", ") // {Last, First}③ 전화번호 표준화
=TEXTJOIN("-",, TEXTSPLIT(SUBSTITUTE(A2," ",""), {"-","."}))④ 로그 ‘키=값’ 파싱 → 2열
=TEXTSPLIT(A2, "=",, TRUE)⑤ 마지막 점(.) 이후 확장자
=TEXTAFTER(A2,".",-1)⑥ N번째 단어
=INDEX(TEXTSPLIT(TRIM(A2)," "), N)⑦ 주소를 시·구·동으로
=TEXTSPLIT(SUBSTITUTE(A2," "," "), " ") // 이중 공백 정리 후 분리⑧ CSV 한 셀에 들어온 데이터 행/열 분리
=TEXTSPLIT(A2, ",", CHAR(10), TRUE)⑨ 범주/태그를 행으로 스필 후 집계
=LET(t, TEXTSPLIT(A2, ","), MAP(UNIQUE(t), LAMBDA(u, SUM(--(TRIM(t)=TRIM(u))))))⑩ URL에서 도메인·쿼리
=TEXTBEFORE(TEXTAFTER(A2,"//"),"/")
=TEXTAFTER(A2,"?")⑪ 국제 공백(CHAR(160)) 제거 후 분리
=TEXTSPLIT(SUBSTITUTE(A2,CHAR(160)," "), " ",, TRUE)⑫ 실패 시 친절 메시지
=IFERROR(TEXTAFTER(A2,"@"), "도메인 없음")스필/예외/성능 체크
- #SPILL! → 스필 영역 비우고 표(Table) 밖에서 시작.
- 연속 구분자 →
ignore_empty=TRUE또는SUBSTITUTE로 정규화. - 대소문자·악센트 →
LOWER/UPPER로 통일 후 분리. - 성능 → 동일 전처리는
LET으로 캐시, 전체열 대신 정확 범위를 사용.
요약
| 목표 | 대표 공식 |
|---|---|
| 앞/뒤 추출 | TEXTBEFORE(text, delim) / TEXTAFTER(text, delim) |
| 다중 구분자 분리 | TEXTSPLIT(text, {"-","/","_"}) |
| N번째/마지막 기준 | instance_num ±값 활용 |
| 누락/실패 처리 | IFERROR(...,"없음"), pad_with |