이번 포스팅에서는 엑셀 공백 제거 3가지 방법, 엑셀 데이터 처리의 가장 기본인 공백(띄어쓰기)을 제거하는 3가지 핵심적인 방법(TRIM, SUBSTITUTE, 찾기 및 바꾸기)과 활용 팁에 대해 정리해보았습니다.
엑셀 작업을 하다 보면 누구나 한 번쯤은 미궁에 빠지는 경험을 합니다. VLOOKUP 함수로 데이터를 찾아오는데 분명히 있는 값임에도 불구하고 계속 #N/A 오류가 발생하고, 피벗 테이블을 만들었더니 똑같은 이름의 항목이 두 개로 나뉘어 집계되며, 필터를 걸어도 데이터가 제대로 정렬되지 않는 상황. 이러한 문제들의 상당수는 바로 우리 눈에 잘 보이지 않는 ‘공백’ 때문에 발생합니다.
이 불필요한 공백들은 다양한 이유로 문제를 일으키는데요, 다른 시스템(ERP, 웹사이트 등)에서 복사하여 붙여넣은 데이터에 섞여 들어오거나, 여러 사람이 수작업으로 데이터를 입력하는 과정에서 실수로 스페이스바를 한 번 더 누르거나, 혹은 일반적인 공백과는 다른 특수한 공백 문자(예: 웹 공백)가 포함되는 경우가 그렇습니다.
이처럼 데이터의 앞, 뒤, 혹은 단어 사이에 숨어있는 공백은, 컴퓨터에게 ” itmanual.net”과 “itmanual.net” 그리고 “itmanual .net”을 모두 완전히 다른 값으로 인식하게 만듭니다. 결국 이는 함수 오류, 데이터 분석의 왜곡, 정렬 및 필터링 문제 등으로 이어지게 됩니다.
이번 포스팅에서는 이러한 엑셀 공백 제거 3가지 방법 – TRIM 함수, SUBSTITUTE 함수 및 찾기/바꾸기와 활용 팁에 대해 정리해보았습니다.
엑셀 공백 제거 방법 (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! 오류가 발생합니다. 따라서 정리된 데이터 열을 복사하여 ‘값으로 붙여넣기’를 통해 수식이 없는 순수한 텍스트 값으로 변환해 주는 과정을 거치는 것이 좋습니다.
이번 포스팅에서는 엑셀 공백 없애기 3가지 방법 – TRIM 함수, SUBSTITUTE 함수 및 찾기/바꾸기와 활용 팁에 대해 정리해보았습니다.
- 핵심 요약:
- 일반적인 데이터 정리: TRIM 함수가 정답. (앞뒤 공백, 단어 사이 중복 공백 제거)
- 모든 띄어쓰기 제거: 찾기 및 바꾸기 (Ctrl+H) 또는 SUBSTITUTE 함수가 가장 빠름.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
Excel 엑셀 증감표시 숫자 색상 기호 적용 꿀팁 10초 해결 방법(▲▼ 기호 색깔 넣기)
Excel 엑셀 와일드 카드 3가지 기호로 마스터하는 문자 숫자 찾기 꿀팁