☰ Categories

Categories

Build a data cleanup procedure

Classifies the problems actually present in your sample and orders the fixes so they do not corrupt each other.

CategoryOffice work › Spreadsheets
TagsReformattingAnalyzingOffice workerSpreadsheet
Prompt
Build a procedure for cleaning this messy data.

1. **Classify the problems in my sample** — whitespace, inconsistent notation, mixed formats, duplicates, missing values.
2. Per type, the fix — formula, feature, or find-and-replace — with which is right and why.
3. **Set the execution order.** *Warn where doing steps out of order corrupts the data.*
4. Mark irreversible steps with `[copy the original first]`.
5. **Collect separately what cannot be fixed automatically** and needs a human decision.
6. **Do not include problem types that are not in my data.**

Then: what to check after cleaning, to confirm nothing was silently dropped.
After pasting, fill in the fields at the bottom (Sample · Target format)

What this prompt does

Cleanup steps run in the wrong order destroy data. This orders them, marks the irreversible ones, and separates what needs human judgment.

Real outputs by model

Read full answers

We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.

Input used
Sample: 고객명 칸에 '김민준 ', ' 이서연', '박도윤' 처럼 앞뒤 공백 혼재; 전화번호가 '010-1234-5678', '01098765432', '010.1111.2222'로 형식 제각각; 가입일이 '2025-01-15', '25.1.15', '2025/1/15'로 혼재; 동일 고객이 두 줄로 중복 입력됨; 일부 행은 이메일 칸이 빈칸

Target format: 전화번호는 010-0000-0000 형식으로 통일, 가입일은 YYYY-MM-DD로 통일, 중복 행 제거, 빈 이메일은 '미기재'로 표시
gpt-5.6-luna (codex CLI · 에이전트 하네스) · Run on 2026-09-24

1. 샘플에서 확인된 문제

문제 유형 샘플 위험
앞뒤 공백 '김민준 ', ' 이서연' 검색·중복 판정 실패
전화번호 표기 불일치 하이픈, 숫자만, 마침표 형식 불일치·문자열 비교 실패
가입일 형식 혼재 2025-01-15, 25.1.15, 2025/1/15 날짜 정렬·기간 계산 오류
중복 행 동일 고객이 두 줄 고객 수·집계 중복
빈 이메일 이메일 칸 빈칸 빈 값과 미기재 상태 구분 불가

2. 실행 순서

0. 원본 보존

원본 시트를 복사하고, 작업용 시트에서 진행합니다.

이후 원본 열은 보존하고 정제 고객명, 정제 전화번호, 정제 가입일, 정제 이메일 같은 보조 열을 만드는 것이 안전합니다.

1. 앞뒤 공백 제거 — 수식

고객명에 다음과 같은 수식을 사용합니다.

=TRIM(A2)

TRIM은 앞뒤 공백을 제거하고, 연속된 일반 공백도 정리합니다.

전화번호와 이메일에도 우선 TRIM을 적용합니다. 공백 제거를 나중에 하면 중복 판정이나 빈칸 판정이 잘못될 수 있습니다.

2. 전화번호 표준화 — 수식

전화번호의 하이픈, 마침표, 공백을 제거한 뒤 목표 형식으로 표시합니다.

=LET(
 x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"-",""),".","")," ",""),
 IF(AND(LEN(x)=11,LEFT(x,3)="010"),
    TEXT(--x,"000-0000-0000"),
    "검토 필요"
 )
)

결과 예시:

  • 010-1234-5678 → 010-1234-5678
  • 01098765432 → 010-9876-5432
  • 010.1111.2222 → 010-1111-2222

전화번호는 숫자로 저장하면 앞의 0이 사라질 수 있으므로, 최종적으로는 텍스트로 보존하는 것이 안전합니다.

3. 가입일 표준화 — 날짜 변환 기능 또는 수식

날짜는 찾기 및 바꾸기만으로 처리하면 위험합니다. 예를 들어 25.1.15를 문자열로 바꾸는 것과 실제 날짜 값으로 변환하는 것은 다릅니다.

먼저 날짜 열의 값들을 날짜 형식으로 변환한 뒤, 셀 표시 형식을 다음으로 지정합니다.

yyyy-mm-dd

스프레드시트의 날짜 변환 기능을 사용하거나, 원본 형식이 명확하다는 전제에서 수식으로 변환합니다.

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(C2,".","-"),"/","-"))

그 다음 셀 표시 형식을 yyyy-mm-dd로 지정합니다.

25.1.15를 2025-01-15로 해석하는 규칙이 확실해야 합니다. 연도 25의 세기를 자동 추정하게 두면 다른 데이터에서는 잘못된 날짜가 될 수 있습니다.

4. 빈 이메일 표시 — 수식

공백 제거 후 이메일이 비어 있으면 미기재로 표시합니다.

=IF(TRIM(D2)="","미기재",TRIM(D2))

이 단계는 실제 이메일 주소와 빈 값을 구분하기 위한 것이므로, 빈칸을 임의의 이메일 주소로 채우는 것과는 다릅니다.

5. 중복 행 식별 — 중복 제거 기능 또는 보조 수식

먼저 중복 여부를 표시합니다.

=COUNTIFS(정제고객명열,정제고객명셀,
          정제전화번호열,정제전화번호셀)

결과가 2 이상이면 중복 후보입니다.

그 후 이름과 전화번호 등 신뢰할 수 있는 기준으로 중복 제거 기능을 사용합니다. 이메일은 빈 값이 미기재로 바뀌므로, 중복 판정의 핵심 기준으로 사용하기 전에 주의해야 합니다.

중복 행 삭제는 [copy the original first]입니다.

중복 제거 전에 전화번호와 고객명의 공백·표기를 먼저 통일해야 합니다. 순서를 바꾸면 같은 고객이 서로 다른 문자열로 남아 중복으로 잡히지 않을 수 있습니다.

6. 최종 열로 확정

정제 결과를 검토한 뒤 필요하면 보조 열을 값으로 붙여넣어 최종 열로 확정합니다.

수식을 값으로 변환하는 작업은 되돌리기 어려우므로 [copy the original first]입니다.

3. 유형별 도구 선택

문제 권장 방법 이유
앞뒤 공백 수식 TRIM 일괄 처리 가능하고 원본 보존이 쉬움
전화번호 표기 불일치 수식 여러 기호를 제거하고 숫자 길이·국번을 검증해야 함
가입일 형식 혼재 날짜 변환 기능 또는 수식 문자열 치환만으로는 실제 날짜가 되지 않을 수 있음
중복 행 중복 식별 후 중복 제거 기능 어떤 행을 남길지 판단이 필요함
빈 이메일 수식 IF 빈 값만 정확히 미기재로 바꿀 수 있음

찾기 및 바꾸기는 전화번호의 단순한 기호 제거에는 사용할 수 있지만, 전체 표준화 방법으로는 권장하지 않습니다. 날짜나 전화번호의 유효성을 검증하지 못하기 때문입니다.

4. 자동으로 확정하지 말고 사람의 판단이 필요한 것

  • 동일 고객의 두 행에 전화번호나 가입일이 서로 다르면 어느 값을 남길지 결정해야 합니다.
  • 25.1.15 같은 두 자리 연도의 세기를 결정해야 합니다. 이 샘플에서는 2025로 볼 수 있지만, 일반 규칙을 정해야 합니다.
  • 이름과 전화번호가 같은 두 행이 정말 중복인지, 가족·공용 전화번호인지 확인해야 합니다.
  • 중복 행에서 이메일처럼 한쪽만 값이 있는 경우, 값을 합칠지 한 행을 삭제할지 결정해야 합니다.
  • 전화번호가 010으로 시작하지 않거나 자릿수가 맞지 않아 검토 필요가 된 값은 원본 확인이 필요합니다.

5. 정제 후 확인할 것

  • 원본 행 수와 정제 후 행 수를 비교합니다.
    • 감소한 행 수가 실제 중복 제거 행 수와 일치해야 합니다.
  • 원본 고객명·전화번호를 기준으로 정제 결과에 대응 행이 모두 있는지 확인합니다.
  • 전화번호가 모두 010-0000-0000 패턴인지 확인합니다.
  • 가입일이 모두 실제 날짜이며 YYYY-MM-DD로 표시되는지 확인합니다.
  • 이메일 빈칸이 의도한 대로 모두 미기재가 되었는지 확인합니다.
  • 정제 후에도 동일 고객 후보가 남아 있는지 다시 중복 검사합니다.
  • 검토 필요, 빈 문자열, 오류값이 남아 있지 않은지 필터링합니다.
  • 집계용 고객 수가 “원본 행 수 − 실제 삭제한 중복 행 수”와 일치하는지 확인합니다.

More in this category

Analyse Énergétique avec DJU, Consommation et Coûts
Betting Prediction
Black Magic Mantra: Unleashing Creativity and Innovation
Build conditional formatting rules
Reconcile two tables whose numbers disagree