정말로 원치 않는 공백 모두 제거하려면? (TRIM ALL 함수?)

정말로 원치 않는 공백 모두 제거하려면? (TRIM ALL 함수?)

테이블 목차

1. 이유: 공백
2. Excel의 TRIM 함수
3. 문자 코드와 ASCII 문자 코드
4. 일반 공백과 비 분리 공백
5. 비 분리 공백을 제거하는 방법
6. SUBSTITUTE 함수 사용
7. TRIM과 SUBSTITUTE 함수 모두 사용
8. 숫자로 인식되지 않는 공백 처리
9. VALUE 함수와 SUBSTITUTE 함수
10. 한 번에 모든 단계 수행하기
11. 결론

공백으로 인한 문제

열 개별 대상에서 데이터를 복사하여 붙여넣은 적이 있나요? 이메일이나 웹 페이지에서 Excel 시트로 데이터를 복사한 후, 해당 범위를 참조하는 모든 공식이 오류를 출력하는 일이 있었을 겁니다. 좀 더 파헤쳐보고 조사를 해보니, 이러한 오류의 공통 원인 중 하나는 원치 않는 공백, 즉 앞뒤로 붙은 공백이 복사될 때 발생하는 것이었습니다. 공백이 추가로 복사되어 Excel로 데이터를 가져 왔을 때 Excel의 TRIM 함수를 사용하여 이러한 추가 공백을 제거할 수 있다는 것을 알게 되었습니다. 🔍

Excel의 TRIM 함수

TRIM 함수를 통해 우리는 주로 원치 않는 공백을 제거할 수 있습니다. 이 함수는 데이터에서 앞뒤에 있는 공백을 제거하여 깨끗한 데이터를 반환합니다. 하지만 이것은 90%의 경우에 작동하지만 언젠가는 작동하지 않을 때도 있습니다. 이번에는 새로운 데이터 세트를 Excel 시트로 복사한 다음 TRIM 함수를 사용하여 적용해 보았지만 이번에는 작동하지 않았습니다. 🔍

그렇다면 왜 작동하지 않는 걸까요? 공식은 작동하는 것처럼 보이는데요. 이 공식의 일부가 반환하는 값을 확인해 보겠습니다. 해당 부분을 강조 표시하고, F9를 눌러 확인해 보면 TRIM 함수를 사용하더라도 여전히 공백이 남아있음을 알 수 있습니다. TRIM 함수는 공백을 제거할 수 있지만 특정 유형의 공백만 제거할 수 있습니다. 이 공백은 캐릭터 코드 또는 ASCII 캐릭터 코드 32로 표시되는 공백입니다. 그리고 다른 공백으로 널리 알려진 Non-breaking space이라고도 하는 문자 코드 160이 있습니다. 컴퓨터 프로그램에서는 이러한 ASCII 캐릭터 코드를 사용하여 참조하는 문자를 식별하는 데 사용됩니다. 그래서 예를 들어, 여기에 공백을 추가하고 Enter 키를 눌러 공백과 관련된 캐릭터 코드를 확인하려면 함수 CODE를 입력한 다음 셀을 참조하여 괄호를 닫고 Enter 키를 누릅니다.

일반 공백의 캐릭터 코드는 32입니다. 우리가 여기서 가져온 캐릭터 공간을 복사하고 CODE 함수를 드래그해 표시하면 이번에는 캐릭터 코드 160을 얻게 됩니다. 이것이 Non-breaking space이며, Outlook에서 데이터를 복사하거나 다른 외부 시스템에서 Excel로 데이터를 가져올 때 마주치게 될 수 있습니다. 이들은 모두 캐릭터 코드 32가 아닌 160일 수 있습니다. 이제 우리는 코드가 있으면 실제적인 캐릭터를 제공하는 Excel 공식을 알고 있습니다. Character 함수라고도 하는 것입니다. 따라서 32를 여기에 참조하면 실제로 일반적인 공백을 받을 수 있으며, 여기에서는 비 분리 공백을 받게 됩니다. 이 둘 모두 우리 눈에는 일반적인 공백으로 보이지만, 컴퓨터의 눈에는 그렇지 않습니다. TRIM 함수는 일반적인 공백을 제거하지만 비 분리 공백을 제거할 수는 없습니다. 여기에 TRIM 모두 함수 조합이 있었으면 좋겠지만 현재는 그런 함수가 없습니다. Excel에 TRIM 모두 기능이 있었으면 좋을 것 같지만 지금은 그렇지 않습니다. 따라서 우리는 SUBSTITUTE 함수를 사용해야 합니다. Substitute 함수는 비 분리 공백을 찾고 해당 공백을 없애기 위해 사용됩니다. 우리는 TRIM 대신 SUBSTITUTE 함수를 사용할 것입니다. 🔍

비 분리 공백을 제거하기 위해 SUBSTITUTE 함수를 사용해 보겠습니다. SUBSTITUTE 함수는 자리에 따라 전달되는 인수가 많아지므로 먼저 변형할 셀을 참조하고(이 경우 비 변방 공백), 대체할 old_text, 즉 비 분리 공백입니다. 개별 문자 코드를 확인하는 것이 타당하기 때문에 해당 문자 코드(160)를 입력하고 new_text, 즉 아무 것도 대체할 값은 없으므로 해당 인자는 비워둡니다. 그런 다음 Enter 키를 누르면 테이블에서 정확한 설명을 받을 수 있습니다. 결과를 아래로 내려보겠습니다. 이 방법은 이러한 데이터에만 작동합니다. 여기서는 작동하지 않습니다. 이것이 왜 그럴까요? 이를 확인하기 위해 강조표시하고 F9를 눌러 여전히 공백이 있는지 확인하겠습니다. 여전히 공백이 있습니다. 그러니까 비 분리 공백을 대체했지만 여전히 일반 공백이 있는 것 같습니다. 그렇다면 TRIM 함수와 SUBSTITUTE 함수를 함께 사용하는 것이 합리적일 것입니다. 모든 데이터에 적용해 보겠습니다. 이제 모든 것이 작동합니다. 그런데 데이터는 어떨까요? 그냥 이것들을 모두 합산할 수 있을까요? 이 셀에 대해 Sum 함수를 사용하면 0이 나옵니다. 여기에 일반 공백이 있고 열 셀에 숫자가 있는 경우, 셀에 대해 기본값 함수를 직접 사용하여 실제 숫자를 반환할 수 있을 겁니다. 그러나 기본 값 함수는 작동하지 않습니다. 그렇다면 TRIM 함수를 사용한 다음 VALUE 함수를 사용하면 될까요? 결과는 또한 작동하지 않습니다. 따라서 여기에서 우리는 SUBSTITUTE 함수를 다시 사용해야 합니다. 이번에는 Old_text가 됩니다. 문자 코드는 어떻게 되었나요? 160이었습니다. New_text는 아무것도 없으므로 해당 인자를 비워 둡니다. 괄호를 닫고 Enter 키를 입력하면 깨끗한 숫자처럼 보이지만 실제로는 숫자가 아닙니다. 텍스트로 나타나며, Excel은 이것을 여전히 텍스트로 인식합니다. 따라서 이제 VALUE 함수를 사용하여 숫자처럼 보이게 할 것입니다. 이제 이들에 대해 아래로 드래그해 보겠습니다. 이제 이들에 대한 합계를 구해 봅시다. 이를 복사하여 붙여넣으면 제대로 작동합니다. 때때로 숫자로 인식되지 않는 경우에 종종 마주치게 됩니다. TRIM 함수를 사용하면 작동하지 않고 VALUE 함수를 사용하면 작동하지 않는 경우에는 SUBSTITUTE 함수를 사용하여 비 변방 공백을 찾고 해당 공백을 아무것도로 대체하면 됩니다. 캐릭터 코드 160을 찾아보고 아무것도 대체하도록 설정합니다. 이를 통해 Excel 목록에서 공백을 제거할 수 있습니다. 하나의 단계로 모두 수행하려면 필요한 경우에만 셀을 만드는 대신 이를 하나의 공식으로 합칠 수 있습니다. 이 방법을 시도해 보겠습니다. 여기에 있는 변숫값을 복사하여 붙여넣고 다만 여러 단계를 비교적 간단하게 유지하려면 전체 범위를 참조하는 대신 C9만 참조하도록 하겠습니다. 그리고 이식 기능이 있는 SUM 함수에 넣어 두는 것이 좋습니다. SUM 함수는 하나의 결과만 반환하기 때문입니다. 이제 Enter 키를 누르면 동적 배열 Excel에서 작동합니다. 셀 범위를 복사하여 붙여넣기를 해주시면 되겠습니다. 이 또한 작동합니다. 그렇게 하여 Excel 목록에서 공백을 제거하는 방법을 알아보았습니다. 이 동영상이 유용하게 생각하시는지요? 만약 그렇다면 좋아요👍를 눌러주세요. 이 채널을 아직 구독하지 않은 경우 구독🔔 버튼을 눌러 업데이트를 받으실 수 있습니다. 다음 동영상에서 뵙겠습니다.