엑셀로 회사 자료를 정리하다 보면 다른 파일이나 시스템에서 가져온 데이터 때문에 예상하지 못한 문제가 생기는 경우가 있습니다.
거래처명이 똑같아 보이는데 조회가 되지 않거나, 전화번호에 하이픈이 섞여 있거나, 여러 셀에 나누어져 있는 내용을 하나의 문장으로 합쳐야 할 때가 그렇습니다. 특히 외부에서 가져온 텍스트는 앞뒤 공백이나 불필요한 문자가 들어 있는 경우가 있어 눈으로 확인하기만 해서는 원인을 찾기 어려울 때도 있습니다.
이럴 때 활용하기 좋은 함수가 TRIM, SUBSTITUTE, TEXTJOIN입니다.
저는 이런 텍스트 데이터를 정리할 때 먼저 불필요한 공백이나 문자를 정리하고, 마지막에 필요한 형태로 합치는 방식으로 접근하는 편입니다.
1. TRIM 함수｜불필요한 공백 정리하기
TRIM은 텍스트의 앞뒤에 있는 불필요한 공백을 제거하고, 단어 사이에 여러 개의 일반 공백이 들어간 경우 하나로 줄여주는 함수입니다. Microsoft는 다른 프로그램에서 가져온 텍스트의 불필요한 공백을 정리할 때 TRIM을 사용할 수 있다고 안내합니다.
기본 사용법
=TRIM(A2)예를 들어 A2에 다음과 같이 공백이 들어 있다고 해보겠습니다.
삼성전자 수식을 적용하면
삼성전자처럼 정리할 수 있습니다.
2. TRIM을 실무에서 사용하는 경우
회사 자료를 정리하다 보면 거래처명이나 담당자명이 서로 다른 파일에 저장되어 있는 경우가 있습니다.
화면에서는 똑같이'삼성전자'라고 보여도 한쪽 데이터에 앞뒤 공백이 들어 있다면 조회나 비교 과정에서 문제가 생길 수 있습니다.
이럴 때 별도의 정리 열을 만들어
=TRIM(A2)를 적용할 수 있습니다.
예시
| 원본 데이터 | 정리 결과 |
| 삼성전자 | 삼성전자 |
| 삼성전자 | 삼성전자 |
| 삼성 전자 | 삼성 전자 |
이처럼 조회나 비교 전에 텍스트를 먼저 정리하는 방식으로 활용할 수 있습니다.
3. TRIM으로 제거되지 않는 공백도 있습니다
여기서 주의할 점이 하나 있습니다.
웹페이지에서 복사하거나 다른 프로그램에서 가져온 데이터에는 일반적인 스페이스바 공백이 아닌 줄 바꿈 없는 공백(non-breaking space)이 들어 있을 수 있습니다.
이 문자는 일반 공백과 달라서 TRIM만으로 제거되지 않을 수 있습니다. Microsoft도 값 160의 줄 바꿈 없는 공백은 TRIM만으로 제거할 수 없다고 설명합니다.
이럴 때는 SUBSTITUTE와 함께 사용할 수 있습니다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))먼저 특수 공백을 일반 공백으로 바꾼 뒤 TRIM으로 정리하는 방식입니다.
4. SUBSTITUTE 함수 - 특정 문자 바꾸거나 제거하기
SUBSTITUTE는 텍스트 안에서 특정 문자열을 찾아 다른 문자열로 바꾸는 함수입니다.
=SUBSTITUTE(텍스트,찾을문자,바꿀문자)특정 문자가 여러 번 들어 있는 경우에는 네 번째 인수를 이용해 몇 번째 항목만 바꿀 것인지 지정할 수도 있습니다.
5. 전화번호의 하이픈 제거하기
예를 들어 A2에
010-1234-5678이 들어 있다고 해보겠습니다.
하이픈을 제거하려면:
=SUBSTITUTE(A2,"-","")결과:
01012345678
다만 전화번호처럼 앞자리 0이 중요한 데이터는 숫자로 변환하면 앞의 0이 사라질 수 있으므로 텍스트로 관리하는 것이 안전합니다.
6. 특정 단어를 다른 단어로 바꾸기
SUBSTITUTE는 기호를 없애는 것뿐 아니라 특정 문자를 다른 문자로 변경할 때도 사용할 수 있습니다.
예를 들어
A거래처 → A업체로 바꾸고 싶다면
=SUBSTITUTE(A2,"거래처","업체")
엑셀의 찾기 및 바꾸기 기능 (ctrl + F) 으로 한 번에 변경할 수도 있지만, 자료가 업데이트될 때마다 같은 규칙으로 자동 처리해야 한다면 SUBSTITUTE를 수식으로 사용하는 방법이 편리합니다.
7. 특정 번째 문자만 바꾸기
SUBSTITUTE의 네 번째 인수를 사용하면 특정 번째로 등장하는 문자만 바꿀 수 있습니다.
=SUBSTITUTE(A2,"/","-",2)예를 들어 A2가
2026/07/27이라면 두 번째 /만 -로 바뀌어:
2026/07-27
다만 실제 업무에서는 날짜를 이런 방식으로 직접 수정하기보다 셀에 들어 있는 값이 실제 날짜인지 확인한 후 날짜 서식을 변경하는 방법이 더 적절한 경우도 있습니다.
즉, SUBSTITUTE는 문자 자체를 바꾸는 함수라는 점을 기억하면 됩니다. 셀서식이 텍스트일 경우에만 해당됩니다. 날짜로 되어있으면 값이 변경되지 않습니다.
8. TEXTJOIN 함수｜여러 셀의 내용을 하나로 합치기
여러 셀에 나누어져 있는 텍스트를 하나의 문장이나 목록으로 합쳐야 할 때는 TEXTJOIN을 사용할 수 있습니다.
=TEXTJOIN(구분자,빈셀무시여부,텍스트1,[텍스트2],...)예를 들어 A2, B2, C2에 각각
- 서울특별시
- 강남구
- 테헤란로 123
이 들어 있다면
=TEXTJOIN(" ",TRUE,A2,B2,C2)결과 서울특별시 강남구 테헤란로 123 처럼 만들 수 있습니다.
TEXTJOIN은 여러 범위나 문자열을 지정한 구분자로 결합할 수 있는 함수입니다. Microsoft의 함수 목록에서도 Excel 2019부터 제공되는 텍스트 결합 함수로 안내하고 있습니다. 이전버전은 사용이 되지 않습니다.
9. 여러 행의 담당자 이름을 하나로 합치기
예를 들어 B2:B10에 담당자 이름이 여러 행으로 들어 있다고 해보겠습니다.
=TEXTJOIN(", ",TRUE,B2:B10)결과:
김철수, 이영희, 박민수
처럼 하나의 셀로 합칠 수 있습니다.
두 번째 인수를 TRUE로 지정하면 빈 셀을 무시할 수 있습니다.
이런 방식은 다음과 같은 상황에서 활용할 수 있습니다.
- 여러 담당자 이름을 한 줄로 표시
- 여러 부서명을 하나의 셀에 정리
- 비고 내용을 하나의 셀로 합치기
- 여러 항목을 쉼표로 나열
10. TEXTJOIN과 &는 어떻게 다를까?
간단한 텍스트 두세 개만 합친다면 & 연산자를 사용해도 됩니다.
=A2&" "&B2하지만 여러 셀이나 범위를 합쳐야 한다면 TEXTJOIN이 훨씬 간결합니다.
예를 들어
=A2&", "&A3&", "&A4&", "&A5보다
=TEXTJOIN(", ",TRUE,A2:A5)처럼 작성하는 것이 관리하기 쉽습니다.
11. 세 함수를 함께 사용하기
실제 자료를 정리할 때는 세 함수를 따로 사용하는 것보다 정리 → 변환 → 결합 순서로 함께 사용하는 경우가 있습니다.
예를 들어 주소가 A2, B2, C2에 나누어져 있고 각 셀에 불필요한 공백이 들어 있다고 해보겠습니다.
=TEXTJOIN(" ",TRUE,TRIM(A2),TRIM(B2),TRIM(C2))이렇게 하면 각 셀의 앞뒤 공백을 먼저 정리한 뒤 하나의 주소로 합칠 수 있습니다.
예시
| 시·도 | 구·군 | 상세주소 |
| 서울특별시 | 강남구 | 테헤란로 123 |
결과 서울특별시 강남구 테헤란로 123
이처럼 데이터를 먼저 정리하고 마지막에 합치는 방식으로 생각하면 함수 조합을 이해하기 쉽습니다.
12. 웹에서 가져온 데이터 정리하기
외부 자료를 복사해 엑셀에 붙여 넣었는데 검색이나 정렬이 제대로 되지 않는 경우가 있습니다.
이럴 때는 다음 순서로 확인해보는 것이 좋습니다.
① 일반 공백 확인
=TRIM(A2)
② 특수 공백이 의심되는 경우
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
③ 줄바꿈 같은 인쇄할 수 없는 문자가 섞인 경우
=TRIM(CLEAN(A2))
13. 실무에서 활용할 수 있는 예시
① 여러 담당자 이름 합치기
=TEXTJOIN(", ",TRUE,C2:C8)→ 여러 담당자를 한 셀에 쉼표로 표시
② 거래처명 공백 정리
=TRIM(A2)→ 앞뒤 불필요한 공백 제거
③ 전화번호 하이픈 제거
=SUBSTITUTE(A2,"-","")→ 010-1234-5678 → 01012345678
④ 특수 공백 정리
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))→ 웹에서 복사한 데이터 정리
⑤ 이름과 직급 합치기
A2에 이름, B2에 직급이 있다면
=TEXTJOIN("",TRUE,TRIM(A2)," ",TRIM(B2)," 드림")결과 홍길동 과장 드림
⑥ 줄바꿈을 공백으로 변경
셀 안에 줄바꿈이 들어간 경우
=TRIM(SUBSTITUTE(A2,CHAR(10)," "))줄바꿈을 일반 공백으로 바꾼 뒤 정리할 수 있습니다.
14. 세 함수 중 어떤 것을 사용해야 할까?
| 업무 상황 | 사용할 함수 |
| 앞뒤 공백 정리 | TRIM |
| 특수 공백까지 정리 | SUBSTITUTE + TRIM |
| 특정 문자 제거 | SUBSTITUTE |
| 특정 문자를 다른 문자로 변경 | SUBSTITUTE |
| 여러 셀을 하나로 합치기 | TEXTJOIN |
| 여러 담당자를 한 줄로 표시 | TEXTJOIN |
| 외부 데이터의 불필요한 문자 정리 | TRIM + CLEAN + SUBSTITUTE |
함수 이름을 모두 외우기보다는 내가 해결하려는 문제가 공백인지, 특정 문자인지, 여러 셀을 합치는 것인지 먼저 구분하면 됩니다.
15. 자주 하는 실수
① TRIM이면 모든 공백이 없어질 거라고 생각하기
TRIM은 일반적인 ASCII 공백을 정리하지만 웹에서 가져온 줄 바꿈 없는 공백은 단독으로 제거하지 못할 수 있습니다.
이 경우
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))처럼 사용할 수 있습니다.
② SUBSTITUTE로 모든 데이터를 한꺼번에 변경하기
SUBSTITUTE는 지정한 문자열을 정확하게 찾아 바꾸는 함수입니다.
따라서 원하지 않는 문자열까지 변경되지 않는지 변경 전·후 결과를 확인하는 것이 좋습니다.
③ TEXTJOIN을 사용하면서 빈 셀 처리 여부를 확인하지 않기
TEXTJOIN의 두 번째 인수는 빈 셀을 무시할지 결정합니다.
예를 들어
=TEXTJOIN(", ",TRUE,A2:A10)처럼 TRUE를 사용하면 빈 셀을 건너뛰면서 값을 연결할 수 있습니다.
④ 텍스트 정리와 실제 데이터 변환을 혼동하기
전화번호의 -를 제거하는 것은 문자열을 정리하는 작업입니다.
날짜를 실제 날짜값으로 변환하거나 숫자 텍스트를 숫자로 바꾸는 것은 별도의 작업입니다.
즉, '보기 좋게 정리했다 = 데이터 형식이 바뀌었다'는 뜻은 아닙니다.
이 부분은 특히 회사 자료를 다른 함수로 계산하거나 조회할 때 주의해야 합니다.
엑셀에서 텍스트가 지저분하게 보인다고 해서 처음부터 복잡한 수식을 사용할 필요는 없습니다.
저라면 먼저 무엇이 문제인지 확인한 다음 함수를 선택합니다.
공백이 문제라면 → TRIM
특정 문자가 문제라면 → SUBSTITUTE
여러 셀을 하나로 합쳐야 한다면 → TEXTJOIN
그리고 외부에서 가져온 데이터라면
TRIM → SUBSTITUTE/CLEAN → 필요한 형태로 결합
순서로 확인합니다.
특히 회사에서 여러 파일의 데이터를 합치거나 ERP·웹 자료를 엑셀로 정리할 때는 데이터를 바로 계산하거나 조회하기 전에 텍스트 형식을 먼저 정리하는 습관을 들여두면 좋습니다.
'직장인 실무 > 엑셀' 카테고리의 다른 글
| VLOOKUP만으로 찾기 힘든 엑셀 데이터 찾는 방법 (0) | 2026.08.19 |
|---|---|
| 엑셀 날짜 계산 방법 TODAY / DATEDIF / WORKDAY로 업무 일정 정리하기 (0) | 2026.08.18 |
| 엑셀 SUMIFS / COUNTIFS 사용법 여러 조건으로 매출, 건수 집계하기 (0) | 2026.07.27 |
| 엑셀 IF 함수 사용법 조건에 따라 결과를 자동으로 표시하는 방법 (0) | 2026.07.24 |
| 엑셀 조건별 평균 구하기 AVERAGEIF로 부서,매출 데이터 쉽게 계산하기 (0) | 2026.07.24 |