🧩 TEXTJOIN, TRIM, SUBSTITUTE 함수 실무 적용 완벽 가이드
지저분한 텍스트 데이터를 정리하고 합치는 실전 노하우
목차
- TEXTJOIN 함수 - 여러 셀을 한 번에 합치기
- TRIM 함수 - 불필요한 공백 제거하기
- SUBSTITUTE 함수 - 특정 문자 바꾸기
- 세 함수를 조합한 실무 활용법
- 실무 케이스 모음 (8가지)
- 세 함수 비교 정리
- 자주 하는 실수
TEXTJOIN은 여러 셀의 텍스트를 원하는 구분자로 이어 붙이는 함수입니다. 예전에는 & 기호로 하나씩 연결했지만, 셀이 많아지면 수식이 길어지고 빈 셀 처리가 번거로웠습니다. TEXTJOIN은 이 문제를 한 번에 해결합니다.
A2에 "서울특별시", B2에 "강남구", C2에 "테헤란로 123"이 들어있다면, 결과는 다음과 같습니다.
B2:B10 범위에 담당자 이름이 흩어져 있고 일부 셀이 비어 있어도, 두 번째 인수를 TRUE로 두면 빈 셀은 자동으로 건너뛰고 값이 있는 셀만 콤마로 이어줍니다.
TIP 두 번째 인수(빈 셀 무시 여부)는 실무에서 거의 항상 TRUE로 씁니다. FALSE로 두면 빈 셀 자리에 구분자만 남아 ", , ,"처럼 지저분한 결과가 나옵니다.
TRIM은 텍스트 앞뒤의 공백을 제거하고, 단어 사이에 공백이 여러 개 있으면 하나로 줄여줍니다. 다른 시스템에서 엑셀로 데이터를 내보낼 때 공백이 섞여 들어오는 경우가 매우 흔한데, 이 공백이 VLOOKUP이나 IF 같은 비교 함수를 실패하게 만드는 원인 중 하나입니다.
A2 셀에 " 삼성전자 "처럼 앞뒤로 공백이 들어있다면, TRIM은 이를 깔끔하게 정리합니다.
거래처 목록에서 VLOOKUP으로 값을 찾는데 분명 존재하는 이름인데 #N/A가 뜬다면, 원본 데이터에 보이지 않는 공백이 섞여 있을 가능성이 큽니다.
비교 대상 텍스트를 TRIM으로 먼저 정리한 뒤 VLOOKUP에 넣으면, 눈에 보이지 않는 공백 때문에 발생하는 매칭 실패를 대부분 해결할 수 있습니다.
주의 TRIM은 일반 공백(스페이스바)만 제거합니다. 웹에서 복사해온 데이터에 포함된 특수 공백(non-breaking space)은 TRIM만으로 제거되지 않을 수 있어, 이 경우 SUBSTITUTE와 함께 써야 합니다.
SUBSTITUTE는 텍스트 안의 특정 문자열을 찾아서 다른 문자열로 바꿔주는 함수입니다. 엑셀의 찾기/바꾸기 기능과 비슷하지만, 수식 안에서 자동으로 처리된다는 점이 다릅니다.
A2에 "010-1234-5678"이 들어있다면, 하이픈을 빈 문자열로 바꿔서 숫자만 남길 수 있습니다.
웹페이지에서 복사한 데이터는 일반 공백이 아니라 유니코드 160번(non-breaking space)인 경우가 많아 TRIM으로 지워지지 않습니다. 이때는 SUBSTITUTE로 먼저 일반 공백으로 바꾼 뒤 TRIM을 적용합니다.
네 번째 인수에 숫자를 넣으면 해당 문자열이 여러 번 등장할 때 몇 번째 것만 바꿀지 지정할 수 있습니다.
A2가 "2026/07/27"이라면, 두 번째로 나오는 "/"만 "-"로 바뀝니다.
핵심 SUBSTITUTE는 대소문자를 구분합니다. "Excel"과 "excel"을 다른 문자열로 인식하므로, 대소문자 구분 없이 바꾸고 싶다면 별도 처리가 필요합니다.
TEXTJOIN, TRIM, SUBSTITUTE는 따로 써도 유용하지만, 실무에서는 세 함수를 겹쳐서 쓰는 경우가 훨씬 많습니다. 데이터를 정리(TRIM, SUBSTITUTE)한 다음 합치는(TEXTJOIN) 흐름이 전형적입니다.
시/도, 구/군, 상세주소 각 셀에 불필요한 공백과 특수문자가 섞여 있는 상황을 가정합니다.
각 셀을 TRIM으로 정리하면서, B2처럼 중간에 공백이 두 번 들어간 경우는 SUBSTITUTE로 먼저 한 칸으로 줄인 뒤 TEXTJOIN으로 합칩니다.
A2 "홍길동", B2 "과장"이 들어있으면 아래와 같은 결과가 나옵니다.
| 업무 상황 | 추천 함수 | 이유 |
|---|---|---|
| 여러 셀 값을 구분자로 합치기 | TEXTJOIN | 빈 셀 자동 무시, 구분자 지정 가능 |
| 앞뒤·중복 공백 제거 | TRIM | 비교/조회 함수 오류 예방 |
| 특정 문자·기호 치환 또는 제거 | SUBSTITUTE | 하이픈, 접두어, 특수공백 등 정밀 치환 |
| 웹에서 복사한 지저분한 데이터 정리 | SUBSTITUTE + TRIM | 특수 공백은 TRIM만으로 부족 |
TEXTJOIN
- 여러 셀을 하나로 합칠 때
- 구분자와 빈 셀 무시 여부 지정
- 범위(A2:A10)도 그대로 인수로 사용 가능
TRIM
- 공백만 정리할 때
- 인수 1개(텍스트)만 필요
- 단어 사이 중복 공백도 1칸으로 축소
SUBSTITUTE
- 특정 문자를 정밀하게 바꿀 때
- n번째 위치 지정 가능
- 대소문자 구분, 중첩 사용 가능
- TEXTJOIN 두 번째 인수를 FALSE로 설정 — 빈 셀까지 구분자로 이어져 ", , ,"처럼 지저분한 결과가 나옵니다. 특별한 이유가 없다면 TRUE로 두세요.
- TRIM으로 특수 공백을 지우려는 시도 — 웹에서 복사한 데이터의 non-breaking space는 TRIM만으로 제거되지 않습니다.
SUBSTITUTE(A2, CHAR(160), " ")로 먼저 바꾼 뒤 TRIM을 적용해야 합니다. - SUBSTITUTE의 대소문자 구분을 간과 — "Excel"을 바꾸는 수식은 "excel"에는 적용되지 않습니다. 대소문자가 섞인 데이터라면 LOWER나 UPPER로 먼저 통일하세요.
- TEXTJOIN 사용 가능 버전 확인 누락 — TEXTJOIN은 엑셀 2019 이상, Microsoft 365에서만 지원됩니다. 구버전 공유 문서라면 CONCATENATE나 & 연결로 대체해야 합니다.
'업무 포인트!' 카테고리의 다른 글
| sumifs, countifs 함수 실무적용! (0) | 2026.07.27 |
|---|---|
| IF, IFS - 비슷하지만 다른 사용 방법 (0) | 2026.07.24 |
| AVERAGIF 조건별 평균 계산하기. (0) | 2026.07.24 |
| COUNTIF 실무에 적용! (0) | 2026.07.22 |
| 실무에서 sumif 함수 활용하기 (0) | 2026.07.15 |