엑셀 중복 문자·단어 선택/치환: 연속·전체 중복까지 한 번에 정리하는 방법
엑셀 중복 문자 치환은 두 가지로 나눌 수 있습니다. 첫째, 연속 중복을 하나로 압축(예: aaabbcc → abc). 둘째, 전체 중복에서 첫 등장만 남기고 나머지를 제거(예: abcaad → abcd). 여기에 단어 단위 중복(TEXTSPLIT)과 범위 중복 선택 후 Replace(찾기/바꾸기) 절차, Power Query 대량 처리까지 실무 레시피를 정리합니다.
빠른 해결(Quick Fix)
① 연속 중복 문자 압축(aaabb → ab)
Microsoft 365/2021 이상
=LET(
s, A2,
ch, MID(s, SEQUENCE(LEN(s)), 1),
prev, VSTACK("", DROP(ch, -1)),
keep, ch<>prev,
TEXTJOIN("",, FILTER(ch, keep))
)
바로 앞 글자와 같은 문자는 제거하여 하나만 남깁니다.
② 전체 중복 문자 제거(첫 등장만 유지, 순서 보존)
Microsoft 365/2021 이상
=LET(
s, A2,
ch, MID(s, SEQUENCE(LEN(s)), 1),
pos, SEQUENCE(LEN(s)),
first, XMATCH(ch, ch, 0),
TEXTJOIN("",, FILTER(ch, first=pos))
)
각 글자의 “첫 위치”와 현재 위치가 같을 때만 남겨 전체 중복을 제거합니다.
③ 단어 중복 정리
- 연속 중복 단어 압축 (예:
red red blue blue→red blue):
=LET(arr,TEXTSPLIT(A2," "),TEXTJOIN(" ",,FILTER(arr, arr<>VSTACK("",DROP(arr,-1))))) - 전체 중복 단어 제거 (첫 등장만 유지):
=LET(arr,TEXTSPLIT(A2," "),pos,SEQUENCE(ROWS(arr)),first,XMATCH(arr,arr,0),TEXTJOIN(" ",,FILTER(arr, first=pos))) - 다른 구분자(콤마, 세미콜론 등)는
TEXTSPLIT(A2,{",",";"})처럼 배열 구분자를 사용하세요.
④ 범위 중복 선택 후 Replace(찾기/바꾸기)
- 조건부 서식 → 셀 강조 규칙 → 중복 값 → 색 지정.
- 필터 버튼(자동필터)을 켜고 색으로 필터 → 중복만 표시.
- Ctrl+H(찾기/바꾸기)로 원하는 값/문자열로 일괄 치환.
예) “중복” 표식을 “(dup)”로 바꾸기 등. - 행 전체 삭제가 목적이면 데이터 → 중복 제거를 사용(행 기준).
왜 이런 문제가 생기나(개념·원리)
- 연속 중복은 “바로 앞 문자와 같은가?” 문제입니다. 한 글자씩 분해해
이전 글자와 비교하면 해결됩니다. - 전체 중복은 “이 글자가 처음 등장인가?” 문제입니다.
XMATCH로 “처음 등장한 위치”를 구해 현재 위치와 같은 것만 남기면 됩니다. - 단어 중복은 문장을
TEXTSPLIT으로 단어 배열로 나눈 뒤 문자와 같은 논리로 처리합니다. - 범위 중복은 셀 값끼리의 중복(행 단위)로, 셀 내부 텍스트 중복과는 다른 문제입니다.
실무 예제
샘플 데이터
| 행 | A(원문) | 의도 |
|---|---|---|
| 2 | aaabbccdde | 연속 중복 압축 → abcde |
| 3 | abcaad | 전체 중복 제거(첫 등장만) → abcd |
| 4 | red red blue blue red | 단어 중복 정리 |
| 5 | 010-1234-5678 | 중복 하이픈 정리(연속 기호) |
| 6 | apple;apple;pear;pear;apple | 세미콜론 구분 단어 중복 |
1) 연속 중복 문자 압축 공식 해설
핵심은 “현재 문자 ≠ 이전 문자”일 때만 유지하는 것입니다.
=LET(
s, A2,
ch, MID(s, SEQUENCE(LEN(s)), 1), /* 문자 배열 */
prev, VSTACK("", DROP(ch, -1)), /* 한 칸 앞 문자와 같은 길이 맞추기 */
keep, ch<>prev, /* 다르면 유지 */
TEXTJOIN("",, FILTER(ch, keep)) /* 남긴 문자 합치기 */
)
2) 전체 중복 문자 제거 공식 해설
배열 위치 1..n과 각 문자의 “첫 위치”가 같을 때만 남깁니다.
=LET(
s, A3,
ch, MID(s, SEQUENCE(LEN(s)), 1),
pos, SEQUENCE(LEN(s)),
first, XMATCH(ch, ch, 0),
TEXTJOIN("",, FILTER(ch, first=pos))
)
3) 단어 중복 정리
- 연속 단어 중복 압축:
=LET(arr,TEXTSPLIT(A4," "), TEXTJOIN(" ",,FILTER(arr, arr<>VSTACK("",DROP(arr,-1))))) - 전체 단어 중복 제거(첫 등장만):
=LET(arr,TEXTSPLIT(A6,";"), pos,SEQUENCE(ROWS(arr)), first,XMATCH(arr,arr,0), TEXTJOIN(";",,FILTER(arr, first=pos)))
4) 기호 연속 중복 정리(하이픈/쉼표)
여러 개의 동일 기호를 하나로 압축하려면 문자를 분해해 같은 방식으로 처리하거나 간단한 치환을 사용합니다.
- 치환 예(하이픈 두 개 이상 → 하나):
=SUBSTITUTE(SUBSTITUTE(A5,"--","-"),"---","-")를 반복 적용하거나, 위의 연속 중복 압축 패턴으로 일반화하면 안정적입니다.
5) 구조화참조 표/동적 배열 확장
범위를 표(Ctrl+T)로 만들고 이름을 tbl, 열 이름이 [Raw]라면 연속 중복 압축을 전체에 적용:
=LET(s, tbl[Raw], ch, MID(s,SEQUENCE(LEN(s)),1),
prev, VSTACK("", DROP(ch,-1)), keep, ch<>prev,
TEXTJOIN("",,FILTER(ch, keep)))
스필링을 고려해 결과 열은 일반 범위에 두고, 또는 Power Query로 영구 변환하세요.
6) Power Query로 대량 처리
- 데이터 > 테이블/범위에서 → Power Query 열기.
- 열 분할 → 구분 기호 기준(공백/세미콜론 등) → 행으로 분할.
- 중복 키 제거(첫 등장만 유지). 연속 중복만 압축하려면 인덱스 추가 후 이전 행과 비교해 필터링.
- 열 그룹화 → 모든 행 → 텍스트 결합으로 다시 합치기.
- 닫기 & 로드로 시트에 반영(재새로고침 가능, 원본 보호).
대체 방법/주의/체크리스트
- 연속 중복과 전체 중복을 구분해 접근하세요. 목적이 다릅니다.
- 데이터에 보이지 않는 공백(CHAR(160))이나 탭/줄바꿈이 섞이면
TRIM/CLEAN/SUBSTITUTE로 먼저 정리하세요. - 범위의 중복 행 삭제는 데이터 → 중복 제거가 가장 빠릅니다. 셀 내부 텍스트 중복과는 별개입니다.
- 정규식이 필요하면 Power Query 또는 Office Script/VBA를 고려하세요.
- 반복 작업은 LAMBDA로 사용자 정의 함수로 감싸 재사용성을 높이세요.
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| 수식이 너무 느림 | 대용량에서 문자 분해/필터 반복 | Power Query 사용 또는 LAMBDA+Helper 열로 단계 분리 |
| 단어 기준이 일정치 않음 | 구분자 혼재(공백/콤마/세미콜론) | TEXTSPLIT(text,{",",";"," "})처럼 다중 구분자 사용 |
| 한글이 깨짐 | 바이트 함수(MIDB 등) 혼용 | 유니코드 기준 MID/LEFT/RIGHT만 사용 |
| Replace로 일부만 바뀜 | 필터/선택 범위가 제한됨 | 조건부 서식으로 색 표시 → 색 기준 필터 → 전체 선택 후 Ctrl+H |