중복 제거를 눌렀는데 중복이 그대로 남아 있는 이유

명단을 정리하려고 엑셀에서 중복된 항목 제거를 눌렀는데, 같은 이름이 여전히 두세 줄씩 남아 있는 경우가 있습니다. 눈으로 보면 글자가 완전히 똑같은데도 그렇습니다.

엑셀이 고장 난 것이 아닙니다. 이름 끝에 보이지 않는 글자가 붙어 있어서 엑셀은 서로 다른 값으로 보고 있는 것입니다. 같은 사람 3명을 15행으로 적은 명단을 만들어 엑셀에서 실제로 중복 제거를 눌러 봤습니다.

 

같아 보이는 값이 사실은 네 가지입니다

웹 페이지나 다른 문서에서 복사해 붙여 넣은 이름에는 눈에 안 보이는 공백이 자주 딸려 옵니다. 종류도 하나가 아닙니다. 같은 '김민수'를 네 가지로 적어 두고 실제 코드를 꺼내 봤습니다.

화면에 보이는 값	실제 코드	끝에 붙은 것
김민수	U+AE40 U+BBFC U+C218	없음
김민수	… U+0020	일반 공백
김민수	… U+00A0	비단절 공백
김민수	… U+3000	전각 공백

비단절 공백은 웹 페이지에서 복사할 때, 전각 공백은 한글 입력 상태에서 스페이스를 눌렀을 때 들어옵니다. 넷 다 화면에는 똑같이 보이지만 엑셀에게는 서로 다른 값입니다.

이 네 가지를 사람마다 섞어 15행짜리 명단을 만들고, 엑셀에서 중복 제거를 단계별로 눌러 봤습니다. 측정 환경은 윈도우 11, 엑셀 2013이며 파이썬 3.12.10에서 COM으로 값을 직접 입력해 실행했습니다.

같은 사람 3명을 15행으로 적은 표에서 중복 제거를 눌렀을 때 남는 행 수 막대 그래프. 원본 15행, 그대로 중복 제거 12행, CHAR(160) 치환 6행, UNICHAR(160) 치환 3행이며 실제 사람 수는 3명이다

 

 

 

그대로 중복 제거를 누르면 12행이 남습니다. 완전히 똑같은 행 3개만 사라지고, 뒤에 다른 공백이 붙은 행들은 서로 다른 값이라 그대로 남습니다.

---

TRIM으로 안 잡히는 공백이 있습니다

공백을 없애는 함수로 흔히 TRIM을 씁니다. 그런데 TRIM이 세 가지 공백을 각각 어떻게 처리하는지 재 보니 결과가 갈렸습니다.

입력한 값                       셀에 저장   TRIM 결과
김민수                          그대로     김민수
김민수 + U+0020 (일반 공백)       그대로     김민수          지워짐
김민수 + U+00A0 (비단절 공백)     그대로     김민수 U+00A0   남음
김민수 + U+3000 (전각 공백)       그대로     김민수          지워짐

일반 공백과 전각 공백은 TRIM이 지웠지만, 비단절 공백은 TRIM을 통과해 그대로 남았습니다. 엑셀이 세 공백을 모두 셀에 그대로 담는다는 것도 함께 확인됐습니다. 입력한 값이 하나도 바뀌지 않고 저장됩니다.

그래서 TRIM만 걸고 중복 제거를 눌러도 비단절 공백이 붙은 행은 계속 살아남습니다. 명단을 아무리 정리해도 인원 수가 안 맞는 이유가 여기 있습니다.

---

CHAR(160)이 아니라 UNICHAR(160)입니다

비단절 공백을 지우는 방법으로 CHAR(160)을 치환하라는 안내를 자주 봅니다. 실제로 걸어 봤더니 남는 값이 6종이었습니다.

CHAR(160) 치환    6종   '김민수' | '김민수\xa0' | '박준호' | '박준호\xa0' | '이서연' | '이서연\xa0'
UNICHAR(160) 치환  3종   '김민수' | '박준호' | '이서연'

CHAR(160)을 쓴 쪽은 비단절 공백이 붙은 값이 그대로 남아 있습니다. CHAR 함수가 유니코드 번호가 아니라 시스템 코드 페이지의 번호를 받기 때문입니다. 한국어 윈도우에서 CHAR(160)은 U+00A0이 아닙니다.

유니코드 번호를 그대로 받는 함수는 UNICHAR입니다. 엑셀 2013부터 쓸 수 있습니다.

 

=TRIM(SUBSTITUTE(A2, UNICHAR(160), " "))

 

이 수식을 걸고 결과를 값으로 굳힌 뒤 중복 제거를 누르니 3행이 남았습니다. 실제 사람 수와 처음으로 맞았습니다. 비단절 공백을 일반 공백으로 바꿔 놓으면 그 다음은 TRIM이 처리해 줍니다.

 

수식 결과를 값으로 굳히는 단계를 빠뜨리면 안 됩니다. 중복 제거는 수식이 아니라 화면에 나온 값을 비교하는데, 수식이 살아 있으면 원본 열을 참조하는 상태라 기대와 다르게 동작할 수 있습니다. 복사한 뒤 값 붙여넣기로 한 번 고정하세요.

 

화면에 보이는 것과 셀에 저장된 것이 다른 문제는 공백만이 아닙니다. 엑셀에 15%로 보이는 값이 실제로는 0.15인 경우나 쉼표가 든 값 때문에 열이 통째로 밀리는 경우도 같은 결의 문제입니다.

 

 

중복 제거 후에도 인원 수가 안 맞는다면, 이름 칸에 =LEN(A2)를 걸어 글자 수부터 세어 보세요. 옆 칸보다 하나 크다면 뒤에 안 보이는 공백이 붙어 있는 것입니다.