엑셀 TEXTSPLIT · TEXTBEFORE · TEXTAFTER — 전화·이메일·SKU 추출 25가지 실무 레시피
검증 기준: Excel 365 최신 함수 기반. 실제 예제로 직접 재현했으며, 초안 정리에 AI 도구를 보조적으로 활용했습니다.
연계 가이드(내부 링크 7개)
1) 치트시트(기본 문법)
// TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
=TEXTSPLIT(A2, ", ")
// TEXTBEFORE/AFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
=TEXTBEFORE(A2, "-") // 첫 '-' 앞
=TEXTAFTER(A2, "-", 2) // 두 번째 '-' 뒤
=TEXTAFTER(A2, {"-","/"}) // 여러 구분자 중 첫 일치 뒤2) 패턴별 레시피 25가지(복붙용)
① 이름 · 이메일 분리
=TEXTBEFORE(A2, " <") // 이름
=TEXTBETWEEN(A2, "<", ">") // 이메일(각괄호)② 이메일 아이디/도메인/최상위도메인
=TEXTBEFORE(A2, "@")
=TEXTAFTER(A2, "@")
=TEXTAFTER(TEXTAFTER(A2, "@"), ".")③ 전화번호에서 숫자만
=LET(t,A2, d,SEQUENCE(LEN(t)),
TEXTJOIN("",,IF(ISNUMBER(--MID(t,d,1)), MID(t,d,1), "")))④ 우편번호 5자리 추출(문장 중)
=TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",-1) // 마지막 두 공백 사이⑤ SKU: 앞 3문자-숫자-옵션
=TEXTBEFORE(A2,"-") // Prefix
=TEXTBETWEEN(A2,"-","-") // 숫자
=TEXTAFTER(A2,"-",2) // 옵션⑥ 여러 구분자 한 번에
=TEXTSPLIT(A2, {", "," | ","/"," · "})⑦ n번째 항목 뽑기
=INDEX(TEXTSPLIT(A2,", "), 1, 3) // 3번째⑧ 좌우 트리밍 + 비가시문자 제거
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))⑨ 제품명에서 괄호 안 옵션 제거
=TEXTBEFORE(A2, " (")⑩ 도메인 루트만(서브도메인 제외)
=LET(d,TEXTAFTER(A2,"@"), TEXTAFTER(d,".",-1)&"."&TEXTAFTER(d,".",-2))⑪ 해시태그 분리
=FILTER(TEXTSPLIT(A2," "), LEFT(TEXTSPLIT(A2," "),1)="#")⑫ 주소에서 시/구/동
=TEXTBEFORE(A2,"구")+ "구" & " " & TEXTBETWEEN(A2,"구 ","동")+ "동"⑬ CSV 한 셀 → 표로
=TEXTSPLIT(A2, ",", CHAR(10), 1) // 줄바꿈 기준 행 분리⑭ 문장 마지막 단어
=TEXTAFTER(A2," ",-1)⑮ 앞·뒤 불필요 접두/접미 제거
=TEXTAFTER(A2, "SKU: ")
=TEXTBEFORE(A2, " - end")⑯ 가격 문자열 → 숫자
=--SUBSTITUTE(SUBSTITUTE(A2,",",""),"₩","")⑰ 중간값 자르기(레이블:값)
=TEXTAFTER(A2, ": ")⑱ 여러 행 합쳐 문장 만들기
=TEXTJOIN(", ",,A2:A10)⑲ 규칙 없는 조합에서 영문/숫자만
=LET(t,A2, s,SEQUENCE(LEN(t)),
TEXTJOIN("",,IF(OR(CODE(MID(t,s,1))={45,95}),MID(t,s,1),
IF(ISNUMBER(--MID(t,s,1)),MID(t,s,1),
IF((CODE(MID(t,s,1))>=65)*(CODE(MID(t,s,1))<=122),MID(t,s,1),"")))))⑳ 날짜 텍스트 표준화
=DATEVALUE(TEXTAFTER(A2," ",-1))㉑ 라벨+값을 2열로 스필
=TEXTSPLIT(A2, ": ", CHAR(10))㉒ 머리글/꼬리글 제거 후 본문만
=TEXTBETWEEN(A2,"---START---","---END---")㉓ URL에서 파라미터 값
=TEXTAFTER(TEXTBEFORE(A2,"&",-1),"id=")㉔ 제품명 + 규격 분할
=TEXTSPLIT(A2," ",,1) // 연속 공백 무시㉕ 분리 후 고유 리스트
=SORT(UNIQUE(TOCOL(TEXTSPLIT(A2:A,", "),1)))3) 사전 정제(공백·비가시문자)
- 불가시 공백(CHAR(160)) →
SUBSTITUTE로 통일 - 여러 공백 →
TEXTSPLIT에 ignore_empty=1 적용 - 대소문자 표준화 →
UPPER/LOWER/PROPER
4) FILTER/UNIQUE/XLOOKUP과 결합
// 해시태그에서 고유 태그 정렬
=SORT(UNIQUE(TOCOL(FILTER(TEXTSPLIT(A2:A," "),
LEFT(TEXTSPLIT(A2:A," "),1)="#"),1)))// 이메일 도메인별 고객 수
=LET(d, TEXTAFTER(B2:B,"@"), SORT(BYROW(UNIQUE(d), LAMBDA(x, COUNTIF(d,x))),2,-1))5) 오류·성능 체크리스트
- #VALUE! — 구분자 불일치 →
IFERROR로 기본값 지정 - 대량 데이터 → Power Query로 전처리 후 TEXT* 적용
- 스필 참조는
=A2#로 범위를 안전하게 사용
6) 재방문 과제 & 템플릿
과제 A — “상품명(색상/사이즈)” 일괄 분리
=LET(x,TEXTBETWEEN(A2,"(",")"), HSTACK(TEXTBEFORE(A2," ("), TEXTBEFORE(x,"/"), TEXTAFTER(x,"/")))과제 B — 고객 메모에서 이메일/전화 자동 추출
// 이메일
=FILTER(A2:A, ISNUMBER(SEARCH("@",A2:A)))
// 전화(숫자만 9자리 이상)
=FILTER(A2:A, LEN(LET(t,A2:A, TEXTJOIN("",,IF(ISNUMBER(--MID(t,SEQUENCE(LEN(t)),1)),MID(t,SEQUENCE(LEN(t)),1),""))))>=9)이 글을 북마크하고, 다음 글 “LEFT/RIGHT/MID & FIND/SEARCH 콤보 심화”로 이어가면 텍스트 파이프라인이 완성됩니다.