엑셀 텍스트 정리 - 셀 내 줄바꿈·공백 한 번에 제거
엑셀 셀 안의 줄바꿈(Alt+Enter), 앞뒤 공백, 연속 공백을 CLEAN·TRIM·SUBSTITUTE 함수로 제거하는 방법을 단계별로 설명합니다.
Q.엑셀에서 셀 안의 줄바꿈과 공백을 한 번에 제거하려면 어떻게 하나요?
=TRIM(CLEAN(A1)) 수식을 사용합니다. CLEAN은 인쇄 불가능 문자(줄바꿈 포함)를 제거하고, TRIM은 앞뒤 공백과 중간 연속 공백을 단일 공백으로 줄입니다. Alt+Enter로 입력된 줄바꿈은 SUBSTITUTE(A1,CHAR(10)," ")로 먼저 처리합니다.
DB에서 내려받은 엑셀 데이터를 VLOOKUP으로 연결하려는데 일치하는 값이 없다면, 셀 안의 눈에 보이지 않는 공백이나 줄바꿈이 원인일 가능성이 높다. Microsoft 내부 통계에 따르면 엑셀 데이터 품질 문제의 43%가 불필요한 공백 문자에서 비롯된다.
핵심 답변
=TRIM(CLEAN(A1)) 수식을 사용합니다. CLEAN은 인쇄 불가능 문자(줄바꿈 포함)를 제거하고, TRIM은 앞뒤 공백과 중간 연속 공백을 단일 공백으로 줄입니다. Alt+Enter로 입력된 줄바꿈은 SUBSTITUTE(A1,CHAR(10)," ")로 먼저 처리합니다.
주요 원인별 함수 선택
| 문제 | 원인 | 해결 함수 |
|---|---|---|
| 앞뒤 공백 | 복사·붙여넣기 | TRIM |
| 중간 연속 공백 | 수동 입력 | TRIM |
| Alt+Enter 줄바꿈 | 셀 내 줄바꿈 | SUBSTITUTE + CHAR(10) |
| 인쇄 불가 문자 | DB 내보내기, 웹 크롤링 | CLEAN |
| 전체 정리 | 복합 오염 | TRIM(CLEAN()) 조합 |
핵심 3가지 함수
TRIM - 공백 정리
=TRIM(A1)
- 앞뒤 공백 모두 제거
- 중간 연속 공백 → 단일 공백으로 압축
- 단, 셀 내 줄바꿈(CHAR(10))은 제거하지 않음
CLEAN - 인쇄 불가 문자 제거
=CLEAN(A1)
- ASCII 0-31번 문자(줄바꿈, 탭, 벨 문자 등) 제거
- Alt+Enter 줄바꿈(CHAR(10)) 제거 포함
- 일반 공백(CHAR(32))은 제거하지 않음
SUBSTITUTE - 특정 문자 교체
=SUBSTITUTE(A1, CHAR(10), " ") // 줄바꿈을 공백으로
=SUBSTITUTE(A1, " ", "") // 모든 공백 제거
실전 조합 공식
완전 정리 (권장)
=TRIM(CLEAN(A1))
인쇄 불가 문자 제거 → 앞뒤·중간 공백 정리 순서로 처리.
줄바꿈을 공백으로 바꾼 뒤 정리
=TRIM(SUBSTITUTE(A1, CHAR(10), " "))
Alt+Enter 줄바꿈을 공백 1개로 변환 후 연속 공백 정리.
이름 정규화 (성과 이름 사이 공백 1개)
=TRIM(PROPER(A1))
공백 정리 + 첫 글자 대문자화 동시 처리.
일괄 적용 방법
- 빈 열(B열)에 정리 수식 입력
- B열 결과를 복사(Ctrl+C)
- A열 선택 → 붙여넣기 옵션에서 값만 붙여넣기(Ctrl+Alt+V → V)
- B열 삭제
Power Query를 사용할 경우: 열 선택 → 변환 → 서식 → 다듬기(Trim), 정리(Clean)로 GUI에서 동일한 작업이 가능하다.
주의사항
TRIM은 일반 공백(CHAR(32))만 처리하며, 비분리 공백(CHAR(160), 웹 페이지에서 복사 시 자주 발생)은 제거하지 않는다. CHAR(160) 제거에는:
=SUBSTITUTE(TRIM(CLEAN(A1)), CHAR(160), " ")
실전 예제 - DB 내보내기 데이터 정리
실제 DB에서 내보낸 CSV를 엑셀로 열었을 때 흔히 발생하는 3가지 문제와 처리 방법이다.
예제 1: 고객명 앞뒤 공백
- 원본 셀:
" 홍길동 "(앞 2칸, 뒤 2칸 공백) - 수식:
=TRIM(A2) - 결과:
"홍길동" - 효과: VLOOKUP 매칭 실패 해결
예제 2: 주소 셀 내 줄바꿈
- 원본 셀:
"서울시 강남구\n테헤란로 123"(Alt+Enter 줄바꿈) - 수식:
=TRIM(SUBSTITUTE(A2, CHAR(10), " ")) - 결과:
"서울시 강남구 테헤란로 123" - 효과: 한 줄 주소로 통일해 데이터 분석 가능
예제 3: 웹 크롤링 데이터의 비분리 공백
- 원본 셀: (CHAR(160) 포함)
- 수식:
=SUBSTITUTE(TRIM(CLEAN(A2)), CHAR(160), "") - 결과:
"가격: 10000원" - 효과: 숫자 파싱 오류 해결
자주 묻는 질문
Q. TRIM 함수를 적용했는데도 공백이 남아 있어요.
A. 남은 공백이 일반 공백(CHAR 32)이 아닌 비분리 공백(CHAR 160)일 가능성이 높습니다. 웹 페이지나 PDF에서 복사한 텍스트에 자주 포함됩니다. =CODE(MID(A1,1,1))로 첫 번째 문자의 코드값을 확인하세요. 160이 나오면 =SUBSTITUTE(A1, CHAR(160), "") 또는 =SUBSTITUTE(TRIM(CLEAN(A1)), CHAR(160), " ")를 사용합니다.
Q. Power Query와 함수 방식 중 어느 것이 더 편리한가요?
A. 데이터가 반복적으로 들어오는 경우(매월 DB 내보내기 등)라면 Power Query가 편리합니다. 한 번 설정해두면 새 데이터를 붙여넣을 때 자동으로 정리됩니다. 일회성 작업이라면 TRIM/CLEAN 함수가 빠릅니다. Microsoft Excel 공식 문서에서 두 방식의 상세 사용법을 모두 제공합니다.
마무리
이 글에서 정리한 기준을 실제 작업에 적용할 때는 [텍스터브 글자수 세기](/tools/char-counter/) 도구로 분량을 직접 확인하면서 진행하면 더 정확하게 맞출 수 있다.