엑셀 TEXTSPLIT + REGEX 하이브리드: 이메일·전화·경로를 자동 추출·정규화

TEXTSPLIT으로 토큰화하고 REGEX로 정밀 추출·검증하면, 지저분한 문자열에서 이메일·전화·경로를 7분 만에 표준화할 수 있습니다. 아래 수식을 그대로 붙여 넣으면 동일 결과가 재현됩니다.

Quick Fix: 7분 만에 표준화

1. 원본을 Ctrl+T로 테이블화(이름 raw, 열 Text).
2. SUBSTITUTE(CHAR(160), " ") + TRIM + CLEAN 전처리.
3. TEXTSPLIT에 다중 구분자 배열로 토큰화.
4. REGEXEXTRACT/REPLACE/TEST로 이메일·전화·경로를 추출/검증.
5. WRAPROWS + VSTACK/HSTACK으로 표 완성.

원리 요약

• TEXTSPLIT = 큰 단위 분해, REGEX = 정밀 패턴 추출
• 이메일: [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}
• 전화 숫자만: [^0-9] / 한국 10/11자리 포맷 정규식 제공
• 경로 추출: (/[\w\-./?=&%]+)

실무 예제(복붙)

전처리

=LET(
 s, raw[Text],
 t1, SUBSTITUTE(s,CHAR(160)," "),
 t2, TRIM(CLEAN(t1)),
 t2
)

토큰화(TEXTSPLIT)

=LET(
 s, TRIM(CLEAN(raw[Text])),
 toks, TEXTSPLIT(s, {";","|",","}, CHAR(10), TRUE),
 TRIM(toks)
)

이메일/전화/경로 추출

=LET(x,C2#,FILTER(x,REGEXTEST(x,"([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})")))

=LET(x,C2#,nums,REGEXREPLACE(x,"[^0-9]",""),
 y,FILTER(nums,REGEXTEST(nums,"^\d{10,13}$")),
 MAP(y,LAMBDA(n,IF(REGEXTEST(n,"^\d{11}$"),
  REGEXREPLACE(n,"^(\d{3})(\d{4})(\d{4})$","$1-$2-$3"),
  IF(REGEXTEST(n,"^\d{10}$"),
  REGEXREPLACE(n,"^(\d{3})(\d{3})(\d{4})$","$1-$2-$3"),n)))))

=LET(x,C2#,FILTER(x,REGEXTEST(x,"(/[\w\-./?=&%]+)")))

도메인

=LET(mails,D2#,MAP(mails,LAMBDA(m,IFERROR(REGEXEXTRACT(m,"@(.+)$"),""))))

최종 테이블(Email|Phone|Domain|Path)

=LET(mails,D2#,phones,E2#,paths,F2#,doms,G2#,
 tbl,HSTACK(mails,phones,doms,paths), WRAPROWS(tbl,4,""))

LAMBDA 함수

=LAMBDA(rng,
 LET(
  s, TRIM(CLEAN(rng)),
  toks, TEXTSPLIT(s, {";","|",","}, CHAR(10), TRUE),
  t, TRIM(toks),
  mails, FILTER(t, REGEXTEST(t,"([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})")),
  nums, REGEXREPLACE(t,"[^0-9]",""),
  phones0, FILTER(nums, REGEXTEST(nums,"^\d{10,13}$")),
  phones, MAP(phones0,LAMBDA(n,IF(REGEXTEST(n,"^\d{11}$"),
    REGEXREPLACE(n,"^(\d{3})(\d{4})(\d{4})$","$1-$2-$3"),
    IF(REGEXTEST(n,"^\d{10}$"),
      REGEXREPLACE(n,"^(\d{3})(\d{3})(\d{4})$","$1-$2-$3"),n)))),
  paths, FILTER(t, REGEXTEST(t,"(/[\w\-./?=&%]+)")),
  doms, MAP(mails,LAMBDA(m,IFERROR(REGEXEXTRACT(m,"@(.+)$"),""))),
  tbl, HSTACK(mails,phones,doms,paths),
  WRAPROWS(tbl,4,"")
 )
)

대체 방법·주의사항·체크리스트

• 중량 데이터·이질 원본 결합은 Power Query 권장
• Copilot Clean Data로 선정리 후 하이브리드 적용
• 특수 공백(160)은 SUBSTITUTE로 치환 후 TRIM

Troubleshooting

맺음말

엑셀 TEXTSPLIT REGEX 조합으로 긴 문자열을 정규화된 표로 바꾸는 자동화 파이프라인을 완성했습니다. 다음 글에서 UNIQUE/FILTER/SORT 결합으로 중복 제거와 필터링까지 이어갑니다.