메뉴
실무 위키›응용 공식›엑셀 전화번호 형식 통일 공식

엑셀 전화번호 형식 통일 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

구분 기호가 제각각인 전화번호를 010-1234-5678 한 가지 형식으로 맞춥니다. 국가번호가 앞에 붙은 번호도 함께 정리합니다.

인수 설명

01구분 기호를 걷어내고 하이픈으로 맞추기

=TEXT ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( 전화번호, " ", "" ), "-", "" ), "(", "" ), ")", "" ) * 1, 표시 형식 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
전화번호필수정리할 원본 번호입니다. SUBSTITUTE 함수를 네 번 겹쳐 공백·하이픈·여는 괄호·닫는 괄호를 차례로 빈 문자열로 바꿉니다. 바꿀 기호가 없는 셀은 그대로 지나가므로 이미 정리된 번호도 안전합니다.
표시 형식필수자리마다 0을 적고 하이픈을 끼워 넣습니다. 숫자가 형식보다 짧으면 앞자리를 0으로 채우므로 010의 맨 앞 0이 살아납니다. 예) "000-0000-0000"
C2=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"-",""),"(",""),")","")*1,"000-0000-0000")
A
B
C
D
1
이름
원본
통일 결과
2
김서준
010 2431 8890
010-2431-8890
3
이하은
010-7712-3045
010-7712-3045
4
박도윤
(010)3391-6620
010-3391-6620
5
최지호
010 5580 1177
010-5580-1177
6
정리한 값에 1을 곱해 숫자로 바꾸는 것이 핵심입니다. 텍스트를 그대로 넣으면 TEXT 함수가 형식을 적용하지 않고 원래 값을 그대로 돌려줍니다.

02국가번호가 붙은 번호까지 맞추기

=TEXT ( RIGHT ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( 전화번호, " ", "" ), "-", "" ), "+", "" ), 자릿수 ) * 1, 표시 형식 )
인수 설명 자세히 보기
인수구분설명
전화번호필수정리할 원본 번호입니다. SUBSTITUTE 함수로 공백·하이픈·더하기 기호를 걷어내면 +82로 시작하는 번호는 82가 앞에 남습니다.
자릿수필수오른쪽에서 남길 글자 수입니다. 국내 휴대폰 번호는 맨 앞 0을 뺀 10자리이므로 10을 적습니다.
표시 형식필수"000-0000-0000"처럼 적으면 잘라낸 10자리 앞에 0이 채워져 010으로 시작합니다.
C2=TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"-",""),"+",""),10)*1,"000-0000-0000")
A
B
C
D
1
이름
원본
통일 결과
2
김서준
+82-10-2431-8890
010-2431-8890
3
이하은
+82 10 7712 3045
010-7712-3045
4
박도윤
010-3391-6620
010-3391-6620
5
최지호
+82-10-5580-1177
010-5580-1177
6
국가번호가 붙은 번호와 붙지 않은 번호가 섞여 있어도 오른쪽 10자리만 남기므로 결과가 같아집니다.
이 공식이 사용하는 함수
TEXT 함수 SUBSTITUTE 함수 RIGHT 함수

동작 원리

02국가번호가 붙은 번호까지 맞추기

SUBSTITUTE 함수가 구분 기호를 걷어내고, RIGHT 함수가 오른쪽 10자리만 남겨 국가번호를 떼어냅니다.

예제 시트에서 +82-10-2431-8890을 정리하는 C2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

SUBSTITUTE 함수가 구분 기호를 걷어냅니다

공백과 하이픈, 더하기 기호를 차례로 빈 문자열로 바꿉니다. 숫자만 남지만 앞에 국가번호 82가 그대로 붙어 있습니다.

=TEXT ( RIGHT ( SUBSTITUTE ( … ), 10 ) * 1, "000-0000-0000" )
=TEXT ( RIGHT ( "821024318890", 10 ) * 1, "000-0000-0000" )
2

RIGHT 함수가 오른쪽 10자리만 남깁니다

국내 휴대폰 번호는 맨 앞 0을 뺀 10자리입니다. 오른쪽에서 10자리를 잘라내면 국가번호 82가 떨어져 나갑니다.

=TEXT ( RIGHT ( "821024318890", 10 ) * 1, "000-0000-0000" )
=TEXT ( "1024318890" * 1, "000-0000-0000" )
3

TEXT 함수가 앞자리 0을 채워 형식을 입힙니다

1을 곱해 숫자로 바꾼 뒤 형식을 적용합니다. 형식은 11자리인데 값은 10자리이므로 앞자리가 0으로 채워져 010으로 시작합니다.

=TEXT ( 1024318890, "000-0000-0000" )
= 010-2431-8890 결과는 텍스트입니다. 다시 계산에 쓰려면 숫자로 바꿔야 합니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료