운영 현장에서는 주문번호·품목·규격·색상 등이 종종 파일명에 포함되어 있습니다. 규칙만 명확하면 UI 캡처 없이도 수식으로 필요한 속성을 안정적으로 분리하실 수 있습니다. 본 문서는 확장자 제거 -> 키=값 파싱 -> 누락 토큰 처리 -> 열(테이블) 구성의 4단계 절차를 안내드리고, 바로 따라 하실 수 있도록 복사용 수식과 샘플 CSV, 데이터 사전을 함께 제공합니다.
샘플 데이터 (원본과 결과본) 다운로드
- 원본 목록: 03_filenames.csv
- 추출 결과 포함본: 03_filenames_parsed.csv
샘플 데이터 설명
아래 표는 두 파일의 스키마와 예시를 정리한 데이터 사전입니다. 글 본문에 함께 삽입하시면 독자분들이 구조를 빠르게 이해하고 실습을 즉시 진행하실 수 있습니다.
데이터 사전 - 03_filenames.csv (원본)
| 컬럼 | 의미 | 예시 | 형식·비고 |
|---|---|---|---|
filename |
원본 파일명(메타데이터 내장) | ORD-2025-07-0001_item=Pen_size=0.5_color=Blue.pdf |
문자열, ORD-연도-월-번호_item=..._size=..._color=....pdf 규칙 |
파일명 규칙은 “키=값”을 언더스코어(_)로 구분하고, 접두 ORD-YYYY-MM-번호 패턴을 사용하도록 설계했습니다.
데이터 사전 - 03_filenames_parsed.csv (추출 결과 포함본)
| 컬럼 | 의미 | 예시 | 형식·비고 |
|---|---|---|---|
filename |
원본 파일명 | ORD-2025-07-0001_item=Pen_size=0.5_color=Blue.pdf |
문자열 |
order_token |
주문 토큰(연월) | 2025-07 |
ORD- 이후부터 -000 직전까지 |
item |
품목 | Pen |
item=의 값 |
size |
규격 | 0.5 |
size=의 값 |
color |
색상 | Blue |
color=의 값 |
각 열은 수식으로 생성되며, 키가 누락된 경우 공란으로 처리됩니다.
적용 절차와 수식 사용하기
- 확장자 제거 및 전처리
=SUBSTITUTE(A2,".pdf","")
혼용 대소문자 확장자를 고려하시면=SUBSTITUTE(LOWER(A2),".pdf","")처럼 전처리를 권장드립니다. - 주문 토큰 추출(연월)
일반 규칙형:=TEXTBEFORE(TEXTAFTER(A2,"ORD-"),"-000")
정규식 지원 환경(예: Google Sheets):=REGEXEXTRACT(A2,"ORD-([0-9\\-]+)") - 키=값 속성 추출
품목:=TEXTBEFORE(TEXTAFTER(A2,"item="),"_")
규격:=TEXTBEFORE(TEXTAFTER(A2,"size="),"_")
색상:=TEXTBEFORE(TEXTAFTER(A2,"color="),".") - 테이블형 분해(여러 키를 한 번에)
=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,".pdf",""),"_","|"),"|","=")
위 결과는 [키 | 값] 2열 쌍들의 배열을 반환합니다. 키 목록이 길다면 이 방식으로 전체 속성을 행렬로 받아CHOOSECOLS등으로 값 열만 선택해 사용하실 수 있습니다. - 오류·누락 내성
각 추출식에IFERROR(…,"")를 결합해 예외 입력을 공란으로 안전 처리해 주세요.
자주 발생하는 에러 피하기와 검증 체크리스트
- 구분자 우선순위: 언더스코어(
_)·대시(-)·점(.) 혼용 시 치환(SUBSTITUTE) 순서를 주석으로 고정해 주세요. - 대소문자 다양성: 확장자 대소문자(.PDF) 및 키 이름 대소문자 혼용을 대비해
LOWER로 정규화하시면 안전합니다. - 데이터형 강제: 규격(
size)처럼 숫자/문자 혼용 가능 열은 필요 시 숫자 변환을 별도로 적용해 주세요. - 성능: 대부분은
TEXTBEFORE/AFTER만으로 충분합니다. 예외 케이스만 정규식으로 처리하시면 계산량을 줄일 수 있습니다.
운영 팁
대량 처리에서는 결과를 값으로 확정하여 후속 계산을 가볍게 유지하시길 권장드립니다. 또한 “허용 키 목록(예: item, size, color)”을 별도 범위로 관리하고, 발견된 키가 목록에 없을 경우 경고 플래그를 세우면 장기 운영 시 품질이 크게 좋아집니다.
확장하여 사용하기
- 키 집합 확장:
brand=,channel=,region=등 키를 늘려도 같은 규칙으로 파싱됩니다. - 표준 키 맵:
item을 표준 품목 코드표와XLOOKUP으로 연결해 정식 명칭·그룹을 부여하면 집계·리포팅 연계가 쉬워집니다. - 품질 대시보드: 키 누락율, 허용 외 키 등장 빈도, 값 포맷 오류 건수를 주기적으로 집계해 품질 트렌드를 모니터링해 보세요.
검증용 미니 스니펫
-- 키 누락 행수: =COUNTIF(item,"") + COUNTIF(size,"") + COUNTIF(color,"")
-- 정규식/일반식 결과 일치성 표본검사(임의 n행): =order_token_regex=order_token_plain
-- 값 도메인 점검(색상 예): =COUNTIF(color,"<>Blue")+COUNTIF(color,"<>Gray")+COUNTIF(color,"<>Green")+COUNTIF(color,"<>Black")=ROWS(color)결론
파일명 파싱은 간단하지만 효과가 큽니다. 입력 단계에서 구조화를 완료하시면 이후의 집계·검색·검증·리포팅이 모두 가벼워집니다. 위 수식과 샘플 CSV만으로 즉시 재현 가능하며, 규칙을 한 번 표준화해 두시면 다양한 파일명에도 안정적으로 적용하실 수 있습니다.