Excel에서 공백을 제거하는 3가지 방법

Excel에서 광범위한 데이터 세트를 관리할 때 데이터 일관성을 확보하는 것이 중요하며, 불일치를 야기하는 흔한 원인은 공백입니다. Excel에서 불필요한 공백을 처리하는 3가지 방법을 소개합니다. 물론 이것 말고 다른 방법도 있을 수 있습니다. 본문 하단의 [자주 묻는 질문]도 꼭 살펴보시기 바랍니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

(이미지: 아이엑셀러 닷컴)

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문: How to Remove Spaces in Excel
• URL: https://winbuzzer.com/2024/02/17/how-to-remove-spaces-in-excel-xcxwbt/

---

방법 1: TRIM 함수 사용

Excel의 TRIM 함수를 사용하면 데이터에 있는 원치 않는 공간의 일반적인 문제를 해결할 수 있습니다. 텍스트 시작 부분의 공백(선행), 끝 부분(후행) 또는 단어 사이의 불필요한 추가 공백을 제거할 때 TRIM 함수는 유용합니다. 사용하는 방법은 매우 간단합니다.

 

1. 결과를 표시할 셀(예를 들어 B3 셀)을 선택합니다.
2. =trim(이라고 입력하고 공백이 있는 셀(예를 들어 A3 셀)을 지정합니다.
3. Enter 키를 누르면 결과가 표시됩니다.
4. B3 셀 우측 하단의 채우기 핸들을 드래그하여 수식을 필요한 만큼 복사합니다.

 

이렇게 하면 참조 셀의 모든 선행 공백, 후행 공백 및 여분의 공백이 제거됩니다. 필요하다면 TRIM 함수가 사용된 영역을 '복사'한 다음, '텍스트로 붙여넣기'합니다. 이렇게 하면 원본 셀을 바꾸거나 삭제하더라도 TRIM 함수 적용 결과는 유지됩니다.

 

방법 2: SUBSTITUTE 함수 사용

TRIM 함수는 일반적인 공백 제거에 탁월하지만 SUBSTITUTE 함수는 보다 세분화된 접근 방식을 제공합니다. SUBSTITUTE 함수를 사용하면 특정 문자나 공백을 다른 문자로 바꾸거나 완전히 제거할 수도 있습니다. 또한 특정 공백을 제거하거나 다른 문자로 대체하려는 경우 이 기능을 사용하면 필요한 유연성을 얻을 수 있습니다.

Excel의 SUBSTITUTE 함수는 특정 공백이나 문자를 다른 문자로 대체할 수 있으므로 미묘한 데이터 조정에 이상적입니다. 반면에 '바꾸기' 기능(방법 3 참고)은 선택한 데이터의 모든 단일 공백을 신속하게 제거하는 보다 광범위한 도구로, 빠르고 포괄적인 공백 제거에 적합합니다. SUBSTITUTE가 정밀도를 제공한다면 '바꾸기'는 간편함과 효율성을 제공합니다.

 

1. 결과를 표시할 셀(예를 들어 B3 셀)을 선택합니다.
2. =substitute(A3," ","")라고 입력합니다(공백을 제거할 셀이 A3 셀이라고 가정).
3. Enter 키를 누르면 결과가 표시됩니다.
4. B3 셀 우측 하단의 채우기 핸들을 드래그하여 수식을 필요한 만큼 복사합니다.

 

이렇게 하면 참조 셀에서 텍스트를 복사하여 공백이 있는 위치에 상관없이 모든 공백이 제거됩니다. 필요하다면 SUBSTITUTE 함수가 사용된 영역을 '복사'한 다음, '텍스트로 붙여넣기'합니다. 이렇게 하면 원본 셀을 바꾸거나 삭제하더라도 SUBSTITUTE 함수 적용 결과는 유지됩니다.

 

방법 3: 바꾸기 기능 사용

데이터에서 단어 사이의 공백을 포함하여 모든 공백을 제거해야 하는 경우가 있을 수 있습니다. 이러한 경우 Excel의 '바꾸기' 기능을 사용하면 편리합니다. 이 방법을 사용하면 데이터에서 모든 단일 공백을 빠르게 검색하여 다른 문자 또는 아무것도 없는 문자로 바꿀 수 있으므로 효과적으로 공백을 제거할 수 있습니다. 데이터 세트에 공백이 남지 않도록 하는 간단하고 효율적인 방법입니다.

1. 공백을 제거하려는 영역을 범위로 지정합니다.
2. [홈] 탭 - [편집] 그룹 - [찾기 및 선택] - [바꾸기]를 선택합니다. 또는 단축키인 Ctrl + H를 누릅니다.
3. [찾을 내용] 에 공백 ​​문자를 입력하고 [바꾸기]에는 아무것도 입력하지 않은 채 [모두 바꾸기]를 클릭합니다.

 

(이미지: 아이엑셀러 닷컴)

자주 묻는 질문

Q1. 열의 새 데이터 항목에 TRIM 함수가 자동으로 적용되도록 하려면?

Excel 내 열의 새 데이터 항목에 대해 TRIM 함수가 자동으로 적용되도록 하려면 범위를 선택하고 Ctrl + T를 누르거나 [삽입] 탭으로 이동하여 표를 선택하여 데이터 범위를 표로 변환합니다. 데이터에 헤더가 포함된 경우 '내 테이블에 헤더가 있음' 옵션이 선택되어 있는지 확인하세요. 이 테이블 내의 새 열에 =TRIM([@ColumnName]) 과 같은 TRIM 수식을 입력합니다 . 여기서 "ColumnName"은 정리하려는 열의 헤더입니다. Excel 표는 추가된 새 행으로 수식을 자동으로 확장됩니다.

 

Q2. SUBSTITUTE 함수를 사용하여 여러 연속 공백을 단일 공백으로 바꾸려면?

Excel에서 여러 연속 공백을 단일 공백으로 바꾸려면 SUBSTITUTE와 TRIM 함수를 조합해서 사용하세요. =SUBSTITUTE(A1, ” “, “|||”)와 같이 텍스트의 다른 곳에는 나타나지 않는 고유한 문자 시퀀스로 모든 공백을 바꾸는 것부터 시작하세요. 그런 다음 이 결과에 TRIM을 적용하여 여러 연속 고유 문자의 인스턴스를 단일 인스턴스로 줄입니다. 마지막으로 SUBSTITUTE를 다시 사용하여 고유한 문자 시퀀스를 단일 공백으로 다시 바꿉니다.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1, ” “, “|||”)), “|||”, ” “)

 

Q3. 함수를 사용하여 셀 내에서 줄 바꿈을 제거할 수 있나요?

Excel에서 셀 내의 공백과 줄바꿈을 제거하려면 SUBSTITUTE 함수와 CHAR 함수를 결합하세요. 다음 수식을 사용하면 CHAR(10) 및 CHAR(13)로 표시되는 줄바꿈을 공백이나 빈 문자열로 대체하여 효과적으로 줄을 제거합니다.

=SUBSTITUTE(SUBSTITUTE(A1, CHAR(10), " "), CHAR(13), " ")

 

Q4. 통합 문서의 여러 시트에 공백 제거 함수를 한 번에 적용하려면?

Excel에서 여러 시트에 동시에 공간 제거 기능을 적용하려면 VBA 코딩이 필요합니다. 통합 문서의 각 워크시트를 반복하고 지정된 범위 또는 전체 시트에 원하는 기능을 적용하는 VBA 매크로를 작성합니다. 예를 들어 TRIM 기능을 적용하는 간단한 VBA 스크립트는 아래 코드와 같을 수 있습니다. 다음 VBA 코드는 활성 통합 문서의 모든 워크시트를 반복하여 각 시트의 사용된 범위에 TRIM 함수를 적용합니다. 필요에 따라 범위와 함수(예: SUBSTITUTE )을 맞춤설정할 수 있습니다.

Sub TrimSpacesInAllSheets()
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.UsedRange ' Adjust this range as needed
        rng.Value = Evaluate("IF(ROW(" & rng.Address & "),TRIM(" & rng.Address & "))")
    Next ws
End Sub

 

Q5. 텍스트 함수로 공백을 처리하는 가장 좋은 방법은?

Excel에서 LEFT, MID 또는 RIGHT와 같은 텍스트 함수를 사용할 때 먼저 TRIM 함수를 사용하여 데이터를 정리하여 선행 또는 후행 공백을 제거하세요. 내부 텍스트 공간의 경우 제거 또는 교체를 위해 SUBSTITUTE 함수를 고려하세요. 정리된 텍스트를 사용하면 출력에 영향을 미치는 예상치 못한 공백 없이 정확한 결과를 얻을 수 있습니다.

 

Q6. 잘림 방지 공백이 포함된 셀을 어떻게 식별하고 강조 표시하려면?

잘림 방지 공백이 포함된 셀을 강조 표시하려면 =ISNUMBER(FIND(CHAR(160), A1)) 과 같은 수식과 함께 Excel의 조건부 서식을 사용하세요. 이것은 CHAR(160)로 구별되는 잘림 방지 공백이 있는 셀을 강조 표시하여 추가 작업을 위한 식별을 돕습니다.

Q7. 시작과 끝의 공백을 제거하고 내부 공백은 그대로 유지하려면?

Excel의 TRIM 함수는 단어 사이의 내부 공백을 그대로 유지하면서 텍스트 문자열의 시작과 끝에서 공백을 제거하도록 설계되었습니다. "Hello World"와 같은 텍스트의 경우 =TRIM(A1)을 적용하면 내부 단어 간격이 유지되는 "Hello World"가 생성됩니다.

 

Q8. '찾기 및 바꾸기' 기능으로 셀의 특정 부분에서만 공백을 제거하려면?

Excel의 '찾기 및 바꾸기' 기능은 광범위하며 셀 내용의 시작이나 끝 부분에만 공백을 직접 지정할 수는 없습니다. 대상 공백을 제거하려면 선행 및 후행 공백에 TRIM 같은 함수를 사용하거나 보다 구체적인 제어를 위해 텍스트 함수 조합을 사용하세요.

 

Q9. 원본 변경 없이 다른 셀에서 결합된 텍스트에서 공백을 제거하려면?

원본을 변경하지 않고 다른 셀의 텍스트를 결합하고 정리하려면 CONCATENATE 또는 "&" 연산자를 사용하여 셀 참조를 TRIM으로 래핑합니다. 예: =TRIM(A1) & ” ” & TRIM(B1). 이렇게 하면 결합된 텍스트에 원본 셀의 앞뒤 공백이 없어집니다.

Q10. 데이터세트에서 제거된 공백 수를 추적하는 방법은?

LEN 및 SUBSTITUTE 함수를 사용하여 공백 제거 전후의 텍스트 길이를 비교하여 제거된 공백 수를 추적할 수 있습니다. 공백이 포함된 텍스트의 전체 길이를 계산하고 공백 제거 기능을 적용한 후 새 길이를 계산합니다. 길이의 차이는 제거된 공백의 수를 나타냅니다.

Excel과 VBA의 모든 것 아이엑셀러 닷컴 · 강사들이 숨겨 놓고 보는 엑셀러TV