REGEXREPLACE와 REGEXEXTRACT로 규칙성 있는 문자열 정리하기

이 글의 목표 — 현장 데이터에는 규칙이 있으면서도 일정하지 않은 문자열이 자주 등장합니다. 예를 들어 주문 로그 한 줄에 주문번호와 품목, 규격, 색상, 포장 단위가 공백 간격도 들쭉날쭉한 채 섞여 있습니다. 이런 데이터를 클릭으로 나누다 보면 실수가 일어나고 자동화가 어렵습니다. 정규식을 이해하면 규칙을 수식으로 표현해 언제든 같은 결과를 재현할 수 있습니다. 본문에서는 Google Sheets의 REGEXEXTRACT와 REGEXREPLACE를 중심으로 핵심 패턴을 정리하고, Excel에서는 대체 수식으로 같은 효과를 내는 방법을 제시합니다. 스크린샷 없이 따라 하실 수 있도록 샘플 CSV와 결과 CSV를 함께 제공합니다.

파일 내려받기

샘플 원본 CSV: C2_regex_sample.csv
결과 CSV(추출 결과 열 포함): C2_regex_result.csv

샘플 데이터 설명

샘플 파일은 raw 한 칼럼으로 구성되어 있으며 한 줄에 여러 속성이 함께 기록됩니다. 실제로는 발주 시스템이나 스캔 로그에서 흔히 보이는 형태입니다. 예시는 다음과 같습니다.

ORD-2025-07-0001  item=Pen size=0.5  color=Blue  material=PP dims=6x20mm   note=pack-10
ORD-2025-07-0002  item=Notebook size=A4  color=Black  material=Paper dims=210x297mm  note=pack-5
ORD-2025-07-0003  item=Marker size=0.7  color=Green  material=ABS dims=10x50mm  note=pack-12

원본 C2_regex_sample.csv

컬럼	의미	예시	비고
`raw`	한 줄 로그	ORD-2025-07-0012 item=Pad size=A5 color=Gray material=Paper dims=148x210mm note=pack-20	공백 간격이 일정하지 않을 수 있음

결과 C2_regex_result.csv

컬럼	의미	예시	추출 규칙 개념
`order_no`	주문번호	ORD-2025-07-0007	ORD-yyyy-mm-숫자 패턴
`item`	품목	Notebook	item=다음의 연속 토큰
`size`	규격	A4	size=다음의 숫자 또는 문자
`color`	색상	Blue	color=다음의 알파벳
`material`	재질	PP	material=다음의 알파벳
`dims`	치수	210x297mm	숫자 x 숫자 x 옵션 mm 또는 T
`pack_qty`	포장 단위	10	pack-숫자
`clean_label`	표시용 제목	Notebook A4 Black	item size color를 정리해 결합
`sku_key`	SKU 키	NOTEBOOK-BLACK-A4	대문자로 조합

정규식 기반 추출의 핵심 생각법

정규식은 문자 집합과 반복 규칙으로 패턴을 정의합니다. ()로 감싼 부분은 캡처 그룹이 되어 REGEXEXTRACT가 반환합니다. 간격이 일정하지 않더라도 \s+ 같은 공백 패턴으로 유연하게 대응할 수 있습니다. 이 글에서는 안전성과 가독성을 위해 가능한 한 명시적인 패턴을 사용합니다.

Google Sheets 수식 레시피

원본이 A열에 있고 헤더가 1행, 데이터가 2행부터 있다고 가정합니다.

-- 주문번호
=REGEXEXTRACT(A2, "(ORD-\d{4}-\d{2}-\d{4})")

-- 품목
=REGEXEXTRACT(A2, "item=([A-Za-z0-9\.\-_]+)")

-- 규격
=REGEXEXTRACT(A2, "size=([A-Za-z0-9\.]+)")

-- 색상
=REGEXEXTRACT(A2, "color=([A-Za-z]+)")

-- 재질
=REGEXEXTRACT(A2, "material=([A-Za-z]+)")

-- 치수. 2단 또는 3단 숫자 조합 뒤에 mm 또는 T가 올 수 있음
=REGEXEXTRACT(A2, "dims=([0-9]+[xX][0-9]+(?:[xX][0-9]+)?(?:mm|MM|T)?)")

-- 포장 단위 숫자만 추출
=REGEXEXTRACT(A2, "pack-([0-9]+)")

-- 표시용 제목. 품목과 규격과 색상 결합
=PROPER(REGEXEXTRACT(A2,"item=([A-Za-z0-9\.\-_]+)")) & " " &
 REGEXEXTRACT(A2,"size=([A-Za-z0-9\.]+)") & " " &
 PROPER(REGEXEXTRACT(A2,"color=([A-Za-z]+)"))

-- SKU 키. 대문자 결합
=UPPER(REGEXEXTRACT(A2,"item=([A-Za-z0-9\.\-_]+)")) & "-" &
 UPPER(REGEXEXTRACT(A2,"color=([A-Za-z]+)")) & "-" &
 UPPER(REGEXEXTRACT(A2,"size=([A-Za-z0-9\.]+)"))

-- 라인 표준화. 키=값을 키:값으로 치환하고 다중 공백을 한 칸으로
=REGEXREPLACE(
  REGEXREPLACE(TRIM(A2), "(item|size|color|material|dims|note)=", "\1:"),
  "\s+", " "
)

위 수식은 샘플 데이터와 정확히 일치하도록 설계되었습니다. size 값은 숫자와 문자 혼합을 허용하고, dims는 2단 또는 3단 숫자 조합에 mm나 T 접미를 허용합니다. 결과 CSV의 각 열은 이 수식의 반환과 일치합니다.

Excel에서의 대체 구성

현재 Excel에는 REGEXEXTRACT와 REGEXREPLACE가 기본 함수로 제공되지 않습니다. 그러나 TEXTAFTER와 TEXTBEFORE, TRIM 조합만으로 본 샘플과 같은 key=value 구조를 안정적으로 분해할 수 있습니다. 정규식이 꼭 필요한 경우에는 Power Query나 Office Script를 활용하면 됩니다. 우선 수식 기반 대안을 제시합니다.

-- 주문번호
="ORD-" & TEXTBEFORE(TEXTAFTER(A2, "ORD-"), " ")

-- 품목
=TEXTBEFORE(TEXTAFTER(A2, "item="), " ")

-- 규격
=TEXTBEFORE(TEXTAFTER(A2, "size="), " ")

-- 색상
=TEXTBEFORE(TEXTAFTER(A2, "color="), " ")

-- 재질
=TEXTBEFORE(TEXTAFTER(A2, "material="), " ")

-- 치수
=TEXTBEFORE(TEXTAFTER(A2, "dims="), " ")

-- 포장 단위 숫자
=VALUE(TEXTAFTER(A2, "pack-"))

-- 라인 표준화. 다중 공백 접기는 TRIM으로
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"item=","item:"),"size=","size:"),"color=","color:"),"material=","material:"),"dims=","dims:"))

키 구분자가 항상 공백으로 끝나는 구조이기 때문에 TEXTAFTER와 TEXTBEFORE가 안정적으로 작동합니다. 원본 규칙이 바뀌면 구분자만 바꿔 주면 됩니다.

정규식 패턴 설계 노트

토큰 추출은 키=값 다음의 최소 길이 토큰을 캐치하는 방식이 안전합니다. 공백 또는 문자열 끝에서 끊어 주면 예외가 적습니다.
치수처럼 복수의 숫자와 구분자가 반복되는 필드는 (?: ... ) 비캡처 그룹과 ? 옵션을 사용해 가변 길이를 표현합니다.
대소문자 혼용이 있을 수 있으므로 mm와 MM 둘 다 허용했습니다.
결합 라벨은 PROPER로 보기 좋게 만들되, SKU 키처럼 식별 용도는 대문자로 고정해 흔들림을 막습니다.

검증 체크리스트

-- 주문번호 추출 일관성
=COUNTIF(결과[order_no], "ORD-*") = ROWS(결과[order_no])

-- 포장 단위는 모두 숫자인가
=COUNT(TRUE, ISNUMBER(결과[pack_qty]*1)) = ROWS(결과[pack_qty])

-- clean_label과 sku_key의 정보 합치 검사
=AND(
  COUNTIF(결과[clean_label], "*"&결과[item]&"*")=ROWS(결과[item]),
  COUNTIF(결과[sku_key], UPPER(결과[item])&"-"&UPPER(결과[color])&"-"&UPPER(결과[size]))=ROWS(결과[item])
)

검증은 간단할수록 좋습니다. 위의 예시는 핵심 열이 의도한 규칙을 만족하는지를 한 번에 확인하도록 설계했습니다.

자주 생기는 문제와 해결

키와 값 사이의 구분자가 달라진다 — 예를 들어 콜론이 섞여 들어오면 (item|size|...)[=:]처럼 문자 집합으로 허용 범위를 넓히십시오.
값 안에 하이픈이나 점이 들어간다 — 품목 토큰에서 [A-Za-z0-9\.\-_]처럼 허용 문자 집합을 넉넉히 잡으십시오.
치수의 단위가 다양하다 — (mm|cm|T)처럼 단위 집합을 확장하고 후속 계산 단계에서 하나의 기준 단위로 변환하십시오.
Excel에서 정규식이 꼭 필요하다 — Power Query의 Replace Values와 Extract 기능, 또는 Office Script의 String.match와 replace를 사용하면 됩니다. 다만 본문 수식만으로도 본 샘플은 완성됩니다.

작업 흐름에 녹이는 방법

원본 텍스트를 그대로 보관하는 시트를 유지합니다. 언제든 패턴을 바꿔 재처리할 수 있어야 합니다.
추출 수식은 별도 시트에서 관리합니다. 열 추가나 삭제로부터 독립적입니다.
정규식은 가급적 짧고 명시적으로 유지하고, 주석용 범위를 만들어 패턴 의미를 적어 두면 협업이 쉬워집니다.

정리

정규식은 문자열을 다루는 가장 안정적인 도구입니다. REGEXEXTRACT로 필요한 정보를 끊어 내고 REGEXREPLACE로 표준화하면, 같은 규칙이 언제나 같은 결과를 보장합니다. 위의 샘플과 결과 CSV를 내려받아 본문 수식을 그대로 적용해 보시기 바랍니다. 규칙을 코드로 표현하는 순간, 클릭이 사라지고 재현성이 생깁니다. 이것이 현장 업무에서 자동화를 가능하게 하는 가장 단단한 방법입니다.

저작자표시 비영리 변경금지 (새창열림)

스프레드시트 자동화 팩토리