Substitute와 Trim으로 공백 처리하기

0. Substitute / Trim은 언제 쓰나

신생 기업이 아니고서야 회사에 들어가면 보통 과거 데이터들이 빼곡히 쌓여있습니다. 물론 인사이트를 얻을 부분이 아니라고 판단된 데이터들이라면 수집이 안되어 데이터가 없을 수도 있지만 말이죠.

 

정상적으로 데이터를 수집되었다고 보고 인턴이 정리한 엑셀 자료를 보고 받는다고 가정해볼게요. 셀을 보면 생각보다 실수가 많아 보일 수 있습니다. 사람이 하다 보면 아무리 꼼꼼해도 실수가 날 수 있기 마련이기 때문입니다.

 

위와 같은 상황에서 데이터 입력과 관련된 여러 상황이 있을 수 있지만, 이번 포스팅에서는 특히 공백과 관련된 실수들에 어떻게 대응할 수 있는지 알려드리고 합니다. 기계적으로 입력하다 보면 스페이스바를 여러 번 두드릴 수도 있고 해서 생각보다 실수 빈도수가 높기 때문에, 특히 도움이 될 것 같다고 판단했습니다.

1. Substitute / Trim으로 공백 제거하기

Trim을 통해서만으로 공백에 대한 문제는 해결할 수 있습니다. 아래 예제를 보시면 제품명 앞과 뒤로 한 두 칸씩 공백들이 잇는 것을 확인할 수 있습니다. 앞뒤로 공백 이슈가 있을 경우 Trim 함수를 이용하면 아래와 같이 앞뒤 공백들이 모두 사라진 상태로 정상적인 값이 도출되는 것을 확인하실 수 있습니다.

뒤에 있던 공백들에 대해서는 셀 하나하나씩 확인하지 않는 이상 눈에 띄지 않아서 잘 구분이 안될 수 있겠지만, 확실히 앞에 있던 공백들은 사라진 것을 확인하시기 쉽습니다.

 

그런데 이름 사이사이에 만약 공백이 있다면 Trim으로도 해결이 가능할까요? 이미 위 예제에서 보셨던 앞뒤의 공백들만 커버되는 것을 확인하셨으니 답은 하실 거라 생각합니다. 그렇다면 문자 사이사이에 위치한 공백들도 제거하려면 어떻게 하면 좋을까요? 이번에 바로 Substitute라는 함수를 사용하시면 됩니다.

 

수식은 위 사진을 참고하시면 됩니다. 구체적인 부분에 대해서 추가적으로 설명을 드리자면

 

1. Substitute 함수를 입력합니다.

2. 첫 번째 자리에 공백 문제를 해결하고 싶은 셀을 입력해줍니다.

3. 두 번째 자리에는 Char(160)을 입력합니다. 공백을 가리는 수식이라고 봐주시면 됩니다.

4. 세 번째 자리에는 ""을 입력해서 공백 없는 빈칸으로 인식하게 해 줍니다.

5. 마지막으로 엔터를 누르면 셀에 있던 모든 공백들이 사라지게 됩니다.

 

위 함수들을 정리하자면, 실무에서 공백으로 데이터 활용이 힘들 때 Trim이나 substitute 함수로 일일이 각 셀들을 확인하지 않고 빠르게 공백 관련 이슈들을 해결할 수 있다는 장점이 있습니다.

 

Trim은 문자 앞뒤 공백들을 제거하는데 탁월하고, substitute는 셀 내에 존재하는 모든 공백들을 제거하는데 유용하게 사용할 수 있으니, 상황에 맞게 사용하셔서 데이터 정리 시간을 최대한 단축시켜 봅시다.

2. Clean 함수로 줄 바꿈도 함께 해결하기

과거 데이터들을 보다 보면 1개의 셀에 입력할 정보가 생각보다 많거나 이름이 긴 경우 줄 바꿈을 해둔 셀들을 발견하실 수 있습니다. 이름 앞뒤 공백과 줄 바꿈이 있는 상황을 해결하기 위해서 필요한 함수가 바로 clean 함수라고 보시면 됩니다.

 

특히 외국인 직원들이 있는 회사라면 위와 같이 직원 이름이 길 수도 있겠죠? 이러한 상황에서 쉬운 데이터 가공을 위하여 Clean 함수와 함께 사용하여 공백 문제를 해결해보도록 하겠습니다.

 

함수 수식만 보더라도 매우 직관적인데, Clean 함수를 입력하시고 공백과 줄 바꿈 이슈가 있는 셀을 입력해줍니다. 그리고 그 바깥 함수로 Trim을 입력해주시면 됩니다. 위와 같이 입력하면 Trim 함수로 셀 내 앞뒤 공백이 제거가 되고, Clean 함수로 줄 바꿈 부분도 사라지게 됩니다.

 

물론 성과 이름 모두 함께 붙여서 모든 공백을 없애버리고 싶다고 하시는 분들은 그냥 Substitute를 사용하시면 됩니다. 간단하게 Substitute를 입력하시고, 첫 번째 자리에 공백을 제거하고 싶으신 셀을 입력하시고, 두 번째에는 Char(160), 그리고 세 번째 자리에 ""를 입력하시면 줄 바꿈 부분을 포함한 셀 내 모든 공백들이 제거됩니다.