엑셀 데이터 정리 — 텍스트 함수·서식 자동화

1. 데이터 정리가 필요한 이유

실무에서 받는 데이터의 70%는 그대로 쓸 수 없습니다. 앞뒤 공백, 대소문자 불일치, 합쳐진 열, 불필요한 특수문자 등 "지저분한 데이터"로 수식 오류가 발생하거나 피벗테이블이 제대로 작동하지 않습니다. 클로드에게 정리 수식을 요청하면 수십 개의 함수를 외울 필요 없이 즉시 해결할 수 있습니다.

클로드 데이터 정리 요청 황금 패턴:
"데이터 정리 전문가로서 아래 문제를 해결하는 엑셀 수식을 알려주세요.

 현재 데이터 상태: [문제 설명 또는 예시 값]
 예) A2 = '  김 민준  ' (앞뒤 공백, 이름 중간 공백)
     B2 = 'SALES' (모두 대문자)
     C2 = '010-1234-5678' (전화번호에서 숫자만 필요)

 원하는 결과: [원하는 형태]
 추가 조건: 원본 데이터는 유지하고 D, E, F열에 정리된 값 출력"

2. 공백·서식 정규화 함수

【 TRIM — 앞뒤 공백 및 연속 공백 제거 】
원본: "  김 민준  " → 결과: "김 민준"
수식: =TRIM(A2)

【 CLEAN — 인쇄 불가 문자 제거 (시스템에서 가져온 데이터) 】
수식: =CLEAN(A2)

조합 사용 (공백 + 특수문자 동시 제거):
=TRIM(CLEAN(A2))

【 대소문자 변환 】
UPPER  — 모두 대문자: =UPPER(A2)    "hello" → "HELLO"
LOWER  — 모두 소문자: =LOWER(A2)    "HELLO" → "hello"
PROPER — 첫글자 대문자: =PROPER(A2) "john smith" → "John Smith"

【 SUBSTITUTE — 특정 문자 대체 】
전화번호 하이픈 제거:
=SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")
"010-1234-5678" → "01012345678"

주민번호 뒷자리 마스킹:
=LEFT(A2,8)&"*******"
"900101-1234567" → "900101-1*******"

3. 텍스트 추출 함수 — LEFT·RIGHT·MID·FIND

【 클로드 요청 프롬프트 】
"A열에 '홍길동 (영업팀)' 형식의 데이터가 있습니다.
 이름만 추출하는 수식과 팀명(괄호 안)만 추출하는 수식을
 각각 작성해주세요."

【 클로드 생성 수식 예시 】
이름 추출 (공백 앞부분):
=LEFT(A2, FIND(" ",A2)-1)
"홍길동 (영업팀)" → "홍길동"

팀명 추출 (괄호 안):
=MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1)
"홍길동 (영업팀)" → "영업팀"

【 이메일에서 도메인 추출 】
=MID(A2, FIND("@",A2)+1, LEN(A2)-FIND("@",A2))
"user@kakao.com" → "kakao.com"

【 파일 경로에서 파일명만 추출 】
=MID(A2,FIND("★",SUBSTITUTE(A2,"\","★",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))))+1,LEN(A2))
"C:\Users\kim\report.xlsx" → "report.xlsx"

【 날짜 문자열에서 연도·월·일 분리 】
"2024-01-15" 형식일 때:
연도: =LEFT(A2,4)   → "2024"
월:   =MID(A2,6,2)  → "01"
일:   =RIGHT(A2,2)  → "15"

실제 날짜로 변환:
=DATEVALUE(A2)   또는   =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

4. 텍스트 합치기 — TEXTJOIN·CONCAT

【 클로드 요청 프롬프트 】
"A열(성), B열(이름), C열(직책)을 합쳐서
 '김민준 팀장님' 형식으로 만드는 수식을 알려주세요.
 빈 셀이 있어도 공백 없이 자연스럽게 합쳐지도록 해주세요."

【 클로드 생성 수식 예시 】
기본 CONCAT:
=A2&B2&" "&C2&"님"

TEXTJOIN (빈 셀 무시 가능, Excel 2019+):
=TEXTJOIN(" ",TRUE,A2,B2,C2)&"님"
→ 빈 셀은 자동 무시하고 구분자(" ") 추가

여러 행 합치기:
=TEXTJOIN(", ",TRUE,B2:B10)
→ B2~B10의 이름을 "김민준, 이수진, 박철수, ..." 형식으로

주소 합치기:
=TEXTJOIN(" ",TRUE,D2,E2,F2,G2)
→ 시/구/동/번지를 빈 셀 제외하고 자동 합치기

5. 조건부 서식 자동화 프롬프트

【 클로드 요청 프롬프트 — 조건부 서식 규칙 작성 】
"엑셀 조건부 서식에서 사용할 수식을 작성해주세요.
 적용 범위: A2:F100
 규칙:
 1) D열(매출달성률)이 100% 이상인 행 전체를 연두색으로
 2) D열이 80% 미만인 행 전체를 빨간색으로
 3) 오늘 날짜 기준 마감일(F열)이 3일 이내인 행을 노란색으로
 각 조건부 서식 수식과 설정 방법을 단계별로 알려주세요."

【 클로드 생성 수식 예시 】
규칙 1 — 100% 이상 달성 (연두색):
적용 범위: $A2:$F2 (전체 행에 적용 시 열은 절대참조)
수식: =$D2>=1

규칙 2 — 80% 미만 (빨간색):
수식: =$D2<0.8

규칙 3 — 마감 3일 이내 (노란색):
수식: =AND($F2-TODAY()<=3,$F2>=TODAY())

데이터 바 / 색조 척도 설정 안내:
홈 → 조건부 서식 → 데이터 막대 → 추가 규칙
최솟값: 숫자 0 / 최댓값: 숫자 최대치 지정

6. 데이터 유효성 검사 설정 프롬프트

【 클로드 요청 프롬프트 】
"엑셀 데이터 유효성 검사 설정을 도와주세요.
 ① B열: 드롭다운 목록 (영업/마케팅/개발/HR 중 선택)
 ② C열: 1~100 사이 숫자만 입력 가능, 범위 초과 시 오류 메시지
 ③ E열: 날짜 형식만 허용, 과거 날짜 입력 불가
 각 설정을 어디서 어떻게 하는지 단계별로 알려주세요."

【 클로드 답변 형식 예시 】
① 드롭다운 목록:
   데이터 탭 → 데이터 유효성 검사 → 허용: 목록
   원본: 영업,마케팅,개발,HR

② 숫자 범위:
   허용: 정수 / 데이터: 사이 / 최솟값: 1 / 최댓값: 100
   오류 메시지: "1~100 사이 숫자만 입력하세요"

③ 미래 날짜만:
   허용: 날짜 / 데이터: 크거나 같음 / 시작 날짜: =TODAY()

관련 검색어  ·  엑셀텍스트함수, TRIM엑셀, TEXTJOIN활용, 조건부서식수식, 데이터유효성검사, 엑셀데이터정리, LEFT MID RIGHT엑셀