엑셀 제각각인 전화번호 한 형식으로 맞추기

연락처를 여러 곳에서 받아 한 표에 모으면 모양이 제각각입니다. 어떤 줄은 "010 0000 2001", 어떤 줄은 "010.0000.2002", 어떤 줄은 앞의 0이 사라진 "1000002003"입니다. 이대로 두면 같은 번호인데도 중복 찾기에 걸리지 않고, 문자 발송 프로그램이 정해진 형식을 요구할 때 일일이 고쳐야 합니다. 연락 명단을 쓰기 전에 번호 모양부터 하나로 맞춰 두면 뒤 작업이 모두 편해집니다.

생활에서도 동창회 주소록, 학부모 단체 연락처처럼 여러 사람이 각자 적어 낸 번호를 정리할 때 똑같이 씁니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다. SUBSTITUTE와 TEXT는 컴활 실기 준비에서도 다루는 문자열 함수라 겹쳐 쓰는 연습으로 좋습니다.

예제 표 — 모양이 제각각인 연락처

맨 위 줄은 열 문자, 맨 왼쪽 칸은 행 번호입니다. 이름과 번호는 모두 가상입니다. 작은따옴표(' ')는 공백이 있다는 걸 보이려고 붙인 표시이고 실제 칸에는 없습니다.

4행은 누군가 01000002003을 그냥 입력한 경우입니다. Microsoft 문서에 따르면 엑셀은 수식과 계산을 위해 숫자 앞의 0을 자동으로 지웁니다. 그래서 칸에는 1000002003만 남습니다.

완성 수식

이름 정리 — C2에 넣고 C10까지 채웁니다.

=TRIM(A2)

번호 정리 — D2에 넣고 D10까지 채웁니다(D2 오른쪽 아래 작은 네모를 끌거나 두 번 누릅니다).

=IFERROR(TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"-",""),".","")),"000-0000-0000"),"확인 필요")

점검 — E2에 넣고 E10까지 채웁니다. 결과가 010으로 시작하지 않으면 "확인"이 붙습니다.

=IF(LEFT(D2,3)="010","","확인")

이 결과는 예제 표를 엑셀 파일로 만들어 파이썬 formulas 라이브러리로 계산해 확인했습니다. 9행과 10행처럼 원래 번호가 틀린 줄은 수식이 고칠 수 없으니, "확인"이 붙은 줄만 골라 원본을 보고 직접 고칩니다.

수식 풀어 보기 — 안쪽부터

2행의 "010 0000 2001"을 예로 가장 안쪽부터 한 겹씩 벗겨 봅니다.

• SUBSTITUTE(텍스트, 찾을 글자, 바꿀 글자)는 찾을 글자를 모두 바꿉니다. 바꿀 글자를 ""(빈 글자)로 두면 지우는 효과가 납니다. 한 번에 한 종류만 바꾸므로 공백·하이픈·점을 지우려고 세 번 겹쳤습니다. 괄호 같은 다른 기호가 섞여 있으면 한 겹 더 씌우면 됩니다.
• VALUE는 숫자처럼 생긴 글자를 진짜 숫자로 바꿉니다. 이 과정에서 앞의 0이 빠지는데, 오히려 그 덕분에 4행처럼 처음부터 0이 빠져 있던 번호와 모양이 같아집니다.
• TEXT(값, 서식)의 서식 "000-0000-0000"에서 0은 "여기에 숫자 한 자리, 없으면 0을 채움"이라는 뜻입니다. 0이 11개이니 10자리 숫자 1000002001 앞에 0이 하나 채워져 "010-0000-2001"이 됩니다. Microsoft 문서에도 TEXT 함수로 "000-00-0000"처럼 앞의 0을 포함한 모양을 만드는 예가 있습니다.
• IFERROR는 10행처럼 영문 O가 섞여 VALUE가 #VALUE! 오류를 낼 때 오류 대신 "확인 필요"를 보여 줍니다.

번호에는 왜 TRIM을 안 썼나요? TRIM은 Microsoft 문서 설명대로 "단어 사이의 공백 하나를 제외한" 공백을 지웁니다. 계산해 보니 =TRIM(" 010 0000 2001 ")의 결과는 "010 0000 2001"로, 가운데 공백이 남았습니다. 번호는 공백을 하나도 남기면 안 되니 SUBSTITUTE로 모두 지우고, TRIM은 띄어쓰기를 살려야 하는 이름 칸에 씁니다. 7행 "한 지호"처럼 이름 가운데 공백이 하나 남는 것도 이 때문입니다.

자주 막히는 곳

SUBSTITUTE(B2,UNICHAR(160),"")

• 정리한 번호를 원래 칸에 덮어쓰고 싶을 때 — D열을 복사한 뒤 선택하여 붙여넣기로 값만 붙여 넣습니다. 그냥 붙여 넣으면 수식째 옮겨져 원본을 지우는 순간 결과가 깨집니다.
• 결과는 글자입니다. TEXT 함수의 결과는 텍스트라서 숫자 계산에는 쓸 수 없습니다. 전화번호는 계산할 일이 없으니 문제 되지 않습니다.
• 휴대전화 11자리 기준입니다. "02-000-0000" 같은 지역번호 전화는 자릿수가 달라 이 서식으로는 모양이 틀어집니다. E열 점검에서 "확인"이 붙으니 그 줄은 따로 봅니다.
• 번호를 처음부터 0이 안 빠지게 입력하려면 — Microsoft 문서 기준으로 숫자 앞에 작은따옴표(')를 붙여 입력하거나, 입력 전에 칸 서식을 "텍스트"로 바꿔 두면 글자로 저장됩니다.

다른 방법

• 보이는 모양만 바꾸기(셀 서식) — 번호가 4행처럼 숫자로 저장돼 있다면, 칸을 선택하고 Ctrl + 1로 셀 서식 창을 열어 사용자 지정에 000-0000-0000을 넣는 방법도 있습니다. 값은 그대로 두고 화면에만 010-0000-2003처럼 보입니다. 다만 공백·점이 섞인 글자 번호에는 효과가 없어서, 모양이 섞인 명단에는 위 수식이 낫습니다.
• 하이픈 없는 11자리로 맞추기 — 문자 발송 프로그램이 하이픈 없는 번호를 원하면 서식을 "00000000000"(0 열한 개)로 바꾸면 됩니다.

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 TRIM·LEN으로 공백 지우고 글자 수 세기 — TRIM 자세히
• 엑셀 VALUE로 텍스트 숫자를 숫자로 바꾸기 — 텍스트로 된 숫자 이야기
• 엑셀 선택하여 붙여넣기 활용 — 수식 결과를 값으로 붙이기
• 엑셀 글 전체 목차 — 중복 찾기 등 목적별 함수 조합 시리즈 모아 보기

참고: SUBSTITUTE 함수 (Microsoft 지원) · TEXT 함수 (Microsoft 지원) · 앞에 오는 0과 더 큰 숫자를 유지 (Microsoft 지원)