엑셀로 데이터를 가져오거나 여러 사람이 함께 한 시트를 작업하다 보면 데이터가 지저분해지기 쉽습니다. 특히 셀 내용의 맨 앞, 맨 뒤, 또는 중간에 불필요한 공백이 들어가는 경우가 많습니다.
이 글에서는 엑셀 셀에 포함된 공백을 제거해 데이터를 깔끔하게 정리하는 다양한 방법을 소개합니다. 각 방법마다 장점이 있으니, 상황에 맞는 가장 적합한 방법을 선택해 활용해 보세요.
TRIM 함수로 앞·뒤·중간 공백 한 번에 제거하기
엑셀에서 공백을 제거하는 가장 기본적인 방법은 TRIM 함수를 사용하는 것입니다. 하나의 수식만으로 텍스트의 선행 공백, 후행 공백, 중간의 연속된 공백까지 모두 정리할 수 있습니다.
수식의 구문은 TRIM(text)이며, 인수에는 셀 참조 또는 실제 텍스트를 입력할 수 있습니다. 단, 이 함수는 단어 사이의 단일 공백은 유지하며 중복된 공백만 제거한다는 점과, 결과가 별도의 셀에 표시된다는 점을 기억하세요.
수식을 입력할 셀을 선택합니다. 예를 들어 A1 셀의 텍스트를 정리하기 위해 B1 셀을 선택했다고 가정해 보겠습니다.
아래 수식에서 셀 참조를 자신의 상황에 맞게 바꿔 입력합니다.
=TRIM(A1)
Enter 키를 눌러 수식을 적용하면 결과가 바로 표시됩니다.
위 예시에서 볼 수 있듯이, TRIM 함수는 A1 셀 텍스트의 앞부분, 중간, 뒷부분에 있던 불필요한 공백을 모두 깔끔하게 제거했습니다.
SUBSTITUTE 함수로 공백 바꾸기
엑셀에서 공백을 없애는 또 다른 방법은 SUBSTITUTE 함수입니다. 이 함수는 기존 공백을 다른 개수의 공백이나 빈 문자열("")로 대체할 수 있으며, 마찬가지로 결과는 별도의 위치에 표시됩니다.
구문은 SUBSTITUTE(text, old, new, instance)이며, 처음 세 개의 인수만 필수입니다. 첫 번째 인수에는 셀 참조나 실제 텍스트를 넣을 수 있고, 특정 위치의 항목만 바꾸고 싶다면 네 번째 인수(instance)를 선택적으로 지정하면 됩니다.
결과를 표시할 셀을 선택합니다. 예를 들어 A1 셀의 공백을 바꾸기 위해 B1 셀을 선택합니다.
다음 수식은 두 칸짜리 공백을 한 칸짜리 공백으로 바꾸는 예시입니다. 첫 번째 따옴표 안에는 공백 두 칸을, 두 번째 따옴표 안에는 공백 한 칸을 입력하세요.
=SUBSTITUTE(A1," "," ")
Enter 키를 눌러 수식을 적용하면 변경된 내용을 확인할 수 있습니다.
공백을 완전히 삭제하고 싶다면 두 번째 따옴표를 비워 두면 됩니다.
=SUBSTITUTE(A1," ","")
찾기 및 바꾸기 기능으로 공백 제거하기
위의 함수들은 공백 제거에 매우 유용하지만, 결과를 별도의 셀에 담아야 하는 번거로움이 있습니다. 원본 셀에서 바로 공백을 제거하고 싶다면 엑셀의 찾기 및 바꾸기 기능을 활용하세요.
찾기 및 바꾸기를 사용하면 제거할 공백 수와 교체할 공백 수를 직접 지정할 수 있고, 선택한 셀 범위 또는 전체 시트에 일괄 적용되며 결과가 원본 데이터 셀에 바로 반영됩니다.
특정 셀이나 범위에만 적용하려면 먼저 해당 범위를 선택한 상태에서 진행하세요. 선택하지 않으면 시트 전체의 공백이 찾아서 바뀝니다.
- 홈 탭으로 이동해 편집 그룹의 찾기 및 선택 메뉴를 열고 바꾸기를 클릭합니다.
- 대화상자가 열리면 찾을 내용 필드에 제거하려는 공백 수를 입력합니다.
- 그다음 바꿀 내용 필드에 교체할 공백 수를 입력합니다. 공백을 완전히 삭제하려면 아래 그림처럼 바꿀 내용 필드를 비워 둡니다.
- 다음 중 하나를 선택합니다.
- 셀을 미리 확인하지 않고 모든 공백을 한 번에 교체하려면 모두 바꾸기를 클릭합니다.
- 교체 전에 해당 셀들을 검토하고 싶다면 모두 찾기를 클릭한 뒤, 준비가 되면 모두 바꾸기를 실행합니다.
- 몇 건이 교체되었는지 알림이 표시되며, 찾기 및 바꾸기 창에서 닫기를 누르면 공백이 제거된 데이터를 확인할 수 있습니다.
파워 쿼리(Power Query)로 공백 없애기
엑셀에서 공백을 제거하는 또 다른 방법은 파워 쿼리를 이용하는 것입니다. 이미 데이터 가져오기나 변환 작업에 파워 쿼리를 사용 중이거나, 텍스트의 시작과 끝에 있는 공백만 제거하고 싶은 경우에 특히 적합합니다.
참고: 앞서 소개한 TRIM 함수와 달리, 파워 쿼리의 Trim 기능은 선행 공백과 후행 공백만 제거하며 중간의 공백은 제거하지 않습니다.
아직 파워 쿼리를 열지 않았다면, 데이터를 선택한 후 데이터 탭으로 이동합니다.
- 데이터 가져오기 및 변환 그룹에서 테이블/범위에서를 선택합니다.
- 팝업 창에서 테이블의 데이터 범위를 확인하고, 헤더가 있는 데이터라면 해당 옵션에 체크한 후 확인을 클릭합니다.
- 파워 쿼리 편집기에서 데이터가 열리면, 여러 열이 있는 경우 수정할 열을 선택합니다. Ctrl 키를 누른 채 각 열을 클릭하면 여러 열을 동시에 선택할 수 있습니다.
- 변환 탭으로 이동해 텍스트 열 그룹의 형식 메뉴를 열고 Trim을 선택합니다.
- 모든 선행 공백과 후행 공백이 제거된 데이터로 업데이트된 것을 확인할 수 있습니다.
- 데이터를 바로 사용하려면 홈 탭에서 닫기 및 로드 메뉴를 열고 다음 중 하나를 선택합니다.
- 닫기 및 로드 대상: 업데이트된 데이터를 배치할 위치를 직접 지정할 수 있습니다.
- 닫기 및 로드: 기존 시트 파일의 새 워크시트에 데이터를 자동으로 배치합니다.
이후 필요에 따라 데이터 서식을 지정하거나, 위치를 옮기거나, 테이블을 일반 셀 범위로 변환할 수도 있습니다.
마무리
이렇듯 엑셀에서 불필요한 공백을 제거하는 방법은 다양합니다. TRIM 함수, SUBSTITUTE 함수, 찾기 및 바꾸기, 파워 쿼리 등 자신에게 맞는 도구를 활용하면 공백이 어디에 있든 데이터를 빠르고 손쉽게 정리할 수 있습니다.
엑셀 셀에서 공백을 제거하는 방법을 익혔다면, 이어서 엑셀에서 중복 행 제거하는 방법도 함께 살펴보세요.