이번 포스팅에서는 엑셀 데이터 처리의 가장 기본이자 핵심인 엑셀 공백 제거 3가지 방법, 엑셀 데이터 처리의 가장 기본인 공백(띄어쓰기)을 제거하는 3가지 핵심적인 방법(TRIM, SUBSTITUTE, 찾기 및 바꾸기)과 활용 팁에 대해 정리해보았습니다.
참고로 아래 글은 아래 테스트 날짜와 기기, 앱 버전에서 작동 여부를 직접 확인 완료 하였습니다.
테스트 날짜 : 25년 10월 10일
윈도우 버전 : Windows 11 Home / 24H2
사용기기 : Microsoft Surface Pro 11th Edition
프로그램명/버전 : Microsoft 365 / Excel / 버전 2508
엑셀 작업을 하다 보면 누구나 한 번쯤은 미궁에 빠지는 경험을 합니다. 신입사원 시절, 웹사이트에서 복사해 온 고객 명단을 가지고 VLOOKUP 작업을 하는데, 분명 눈으로는 똑같은 이름인데도 절반은 #N/A 오류가 떠서 몇 시간을 헤맸던 끔찍한 기억이 있습니다. 그 원인은 바로 우리 눈에 잘 보이지 않는 ‘유령 공백’이었습니다.
이 불필요한 공백들은 다양한 이유로 문제를 일으키는데요, 다른 시스템(ERP, 웹사이트 등)에서 복사하여 붙여넣은 데이터에 섞여 들어오거나, 여러 사람이 수작업으로 데이터를 입력하는 과정에서 실수로 스페이스바를 한 번 더 누르거나, 혹은 일반적인 공백과는 다른 특수한 공백 문자(예: 웹 공백)가 포함되는 경우가 그렇습니다.
이처럼 데이터의 앞, 뒤, 혹은 단어 사이에 숨어있는 공백은, 컴퓨터에게 ” itmanual.net”과 “itmanual.net” 그리고 “itmanual .net”을 모두 완전히 다른 값으로 인식하게 만듭니다. 결국 이는 함수 오류, 데이터 분석의 왜곡, 정렬 및 필터링 문제 등으로 이어지게 됩니다.
이번 포스팅에서는 이러한 엑셀 공백 제거 3가지 방법 – TRIM 함수, SUBSTITUTE 함수 및 찾기/바꾸기와 활용 팁에 대해 정리해보았습니다.
앞선 포스팅에서 정리한 엑셀 필터 관련 글은 아래와 같습니다.
[Excel] 엑셀 자간 줄이기 늘리기 (균등분할 기능이란?)
[핵심 실무 기본] Excel 엑셀 필터 설정 3분 마스터하기
엑셀 중복값 찾기 및 필터 설정, 제거, 추출
엑셀 공백 제거 3가지 방법 완벽 가이드 (TRIM, SUBSTITUTE, 찾기 및 바꾸기)
Excel 엑셀 와일드 카드 3가지 기호로 마스터하는 문자 숫자 찾기 꿀팁
[Excel] 엑셀 표 수식 복사 및 숫자 서식 그대로 붙여넣기 2가지 방법
엑셀 공백 제거 방법 (1) TRIM 함수 활용
엑셀 TRIM 함수는 데이터 클리닝의 가장 기본이 되는 함수입니다. 이 함수는 ① 단어 앞뒤의 모든 공백을 제거하고, ② 단어와 단어 사이의 여러 개 공백은 단 하나만 남기고 모두 제거하는 역할을 합니다.
가령 아래와 같이 불필요한 공백이 들어가 있는 데이터가 있다고 합니다.
| 원본 데이터 (A열) | 설명 |
| ” itmanual. net “ | 앞뒤에 불필요한 공백이 있음 |
| “itm a n u a l . net” | 단어 사이에 불필요한 공백이 여러 개 있음 |
| ” it ma nua l. net” | 앞뒤와 단어 사이에 불필요한 공백 있음 |
| “itma nual. net” | 단어 사이에 불필요한 공백이 길게 있음 |
(1) =TRIM(원본셀) 수식 입력
C4 셀을 선택하고, 수식 =TRIM(A4) 을 입력한 후 Enter 키를 누릅니다. C4 셀의 텍스트에서 양쪽 끝 공백이 모두 제거된 결과가 나타납니다.
(2) 채우기 핸들로 수식 아래로 복사
C4 셀의 오른쪽 하단 모서리에 있는 작은 네모(채우기 핸들)를 마우스로 잡고 아래로 쭉 끌어내려, 모든 데이터에 TRIM 함수를 적용합니다.
(선택) 정리된 데이터를 ‘값’으로 붙여넣기 수식으로 정리된 B열 전체를 복사(Ctrl+C)한 뒤, 원래의 A열에 [마우스 오른쪽 클릭 > 선택하여 붙여넣기 > 값(V)] 을 선택하여 붙여넣으면, 수식이 아닌 깨끗한 텍스트 값으로 원본 데이터를 덮어쓸 수 있습니다.
그러면 아래와 같이 앞뒤 모든 엑셀 공백 제거되고 단어 사이의 공백은 하나만 남기고 데이터가 정제된 것을 확인할 수 있습니다.
참고로 마이크로소프트 고객지원 페이지에서도 trim 함수 사용법에 대해서 가볍게 안내하고 있습니다.
엑셀 공백 제거 방법 (2) 찾기 및 바꾸기
이 엑셀 공백 제거 방법은 단어 사이의 공백까지 포함하여, 셀 안에 있는 모든 종류의 공백을 예외 없이 삭제하고 싶을 때 사용하는 가장 직관적이고 빠른 방법입니다.
(1) 공백을 제거할 데이터 범위 선택
작업을 적용할 셀 범위를 마우스로 드래그하여 선택합니다.
(2) ‘찾기 및 바꾸기’ 실행 (단축키: Ctrl+H)
선택된 상태에서 키보드 단축키 Ctrl + H를 누르면 ‘찾기 및 바꾸기’ 대화상자가 바로 나타납니다.
(3) ‘찾을 내용’에 공백 입력, ‘바꿀 내용’은 비워두기
찾을 내용(N): 키보드의 스페이스바를 한 번 눌러 공백을 입력합니다.
바꿀 내용(E): 아무것도 입력하지 않고 빈칸으로 그대로 둡니다.
(4) ‘모두 바꾸기’ 선택
‘모두 바꾸기(A)’ 버튼을 선택하면, 선택했던 범위 안의 모든 공백 문자가 순식간에 사라지는 것을 확인할 수 있습니다.
엑셀 공백 제거 방법 (3) SUBSTITUTE 함수
SUBSTITUTE 함수는 특정 텍스트를 다른 텍스트로 바꾸는 함수로, TRIM이나 찾기 및 바꾸기로 해결되지 않는 특수한 공백까지 제거할 수 있는 가장 강력한 방법입니다.
모든 공백 제거 (찾기 및 바꾸기와 동일)
=SUBSTITUTE(A4, ” “, “”) A4 셀에 있는 모든 공백(” “)을 빈칸(“”)으로 바꾼다는 의미로, ‘찾기 및 바꾸기’와 동일한 결과를 냅니다.
결과 같은 아래와 같이 모든 공백이 제거 됩니다.
(개인 노하우) 주의사항 및 활용 팁
- 상황에 맞는 최적의 방법 선택하기
- 단순 데이터 정돈 (앞뒤 공백, 단어 사이 여러 공백 제거) → 무조건 TRIM 함수 사용을 추천합니다.
- 모든 띄어쓰기를 없애고 텍스트를 붙여야 할 때 → 찾기 및 바꾸기 또는 SUBSTITUTE 함수를 사용합니다.
- ‘찾기 및 바꾸기’는 원본 데이터를 직접 수정
함수와 달리, ‘찾기 및 바꾸기’는 실행하는 즉시 원본 데이터를 변경하며 이전으로 되돌리기(Ctrl+Z) 외에는 복구할 방법이 없습니다. 중요한 데이터라면 시트 전체를 복사하여 사본을 만든 뒤 작업하는 것이 안전합니다. - 함수 사용 후에는 ‘값으로 붙여넣기’ 습관화
TRIM이나 SUBSTITUTE 함수로 데이터를 정리했다면, 그 결과는 여전히 ‘수식’ 상태입니다. 만약 원본 데이터 열을 삭제하면 #REF! 오류가 발생합니다. 따라서 정리된 데이터 열을 복사하여 ‘값으로 붙여넣기’를 통해 수식이 없는 순수한 텍스트 값으로 변환해 주는 과정을 거치는 것이 좋습니다.
엑셀 공백 제거 관련 자주 묻는 질문 (FAQ)
Q1: TRIM 함수를 썼는데 왜 앞뒤 공백이 그대로 남아있나요?
A1: 위 ‘방법 3’에서 설명한 ‘줄 바꿈 없는 공백’일 가능성이 99%입니다. 이 특수 공백은 TRIM 함수가 인식하지 못합니다. =TRIM(SUBSTITUTE(셀, CHAR(160), ” “)) 수식을 사용하면 완벽하게 해결됩니다.
Q2: 숫자가 입력된 셀에 TRIM 함수를 사용해도 되나요?
A2: 네, 오히려 매우 유용할 수 있습니다. 다른 시스템에서 가져온 숫자 데이터가 종종 텍스트 형식으로 저장되어 있는데(예: ‘ 123 ‘), 이때 TRIM 함수를 적용하면 앞뒤 공백이 사라지면서 텍스트 형식이 숫자 형식으로 자동 변환되어 SUM과 같은 계산이 가능해지는 효과가 있습니다.
Q3: ‘찾기 및 바꾸기’와 SUBSTITUTE 함수의 가장 큰 차이점은 무엇인가요?
A3: ‘찾기 및 바꾸기’는 원본 데이터를 직접 수정하는 ‘작업’입니다. 반면, SUBSTITUTE는 원본 데이터는 그대로 두고, 새로운 셀에 결과물을 생성하는 ‘함수’입니다. 원본 보존이 필요하거나, 다른 함수와 중첩하여 더 복잡한 작업을 해야 할 때는 SUBSTITUTE 함수를 사용해야 합니다.
Q4: 줄 바꿈(엔터 키)도 엑셀 공백 제거 기능으로 없앨 수 있나요?
A4: 네, 가능합니다. 이때도 SUBSTITUTE 함수가 유용합니다. 셀 안의 줄 바꿈 문자(보통 CHAR(10))를 찾아 없애면 됩니다. 수식은 =SUBSTITUTE(A2, CHAR(10), “”) 이며, 이를 통해 여러 줄로 입력된 데이터를 한 줄로 합칠 수 있습니다.
Q5: 함수 사용 후에 꼭 ‘값으로 붙여넣기’를 해야 하나요?
A5: 반드시 해야 하는 것은 아니지만, 데이터 정제 작업의 마무리 단계로 강력히 추천합니다. ‘값으로 붙여넣기’를 하지 않으면 데이터는 계속 수식에 의존하게 되어, 원본 열을 삭제하면 오류가 발생하고 파일이 무거워질 수 있습니다. 정제가 끝난 데이터는 수식 연결을 끊고 순수한 ‘값’으로 만들어두는 것이 가장 안정적입니다.
요약 정리
- 일반적인 데이터 정리 (90%의 경우):
- TRIM 함수가 정답입니다. (텍스트 앞뒤 공백 및 단어 사이 중복 공백 제거)
- 사용법: =TRIM(셀주소)
- 모든 띄어쓰기 완전 제거:
- ‘찾기 및 바꾸기'(Ctrl+H) 또는 SUBSTITUTE 함수가 가장 빠릅니다.
- 사용법: 찾을 내용에 스페이스바, 바꿀 내용은 공백으로 두고 ‘모두 바꾸기’ 실행.
- TRIM으로도 안 지워지는 ‘유령 공백’ 제거 (전문가 팁):
- 웹에서 복사한 데이터에 숨어있는 특수 공백(CHAR(160))이 원인입니다.
- 최종 해결 수식: =TRIM(SUBSTITUTE(셀주소, CHAR(160), ” “))
- 핵심 노하우:
- 데이터 분석 전, 엑셀 공백 제거를 위한 데이터 정제는 선택이 아닌 필수 과정입니다.
- 함수로 공백을 제거한 후에는 반드시 ‘값으로 붙여넣기’를 통해 수식이 없는 순수한 데이터로 변환하는 습관을 들이는 것이 좋습니다.
이 가이드를 통해 엑셀 공백 제거의 모든 것을 마스터하고, 보이지 않는 공백 때문에 발생하는 데이터 오류의 스트레스에서 완전히 벗어나 보시기 바랍니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
Excel 엑셀 증감표시 숫자 색상 기호 적용 꿀팁 10초 해결 방법(▲▼ 기호 색깔 넣기)
Excel 엑셀 와일드 카드 3가지 기호로 마스터하는 문자 숫자 찾기 꿀팁
작성자 : Sean Woo
[전공] HCI(인간-컴퓨터 상호작용)·UX(사용자 경험 연구)
[업무] 신기술 기반 사업 전략·기술 기획·디지털 트랜스포메이션 업무
ITmanual.net에서는 실생활, 업무를 진행하면서 느꼈던,
스마트폰(아이폰·갤럭시)·운영체제·각종 앱/프로그램·IT기기 활용 노하우를 실생활 중심 IT 매뉴얼로 정리합니다.