전화번호 형식 통일 공식
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
구분 기호가 제각각인 전화번호를 010-1234-5678 한 가지 형식으로 맞춥니다. 국가번호가 앞에 붙은 번호도 함께 정리합니다.
인수 설명
01국가번호가 붙은 번호까지 맞출 때 (M365)
=TEXT( --RIGHT( REGEXREPLACE( 전화번호, "[ +-]", "" ), 10 ), 표시 형식 )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
전화번호선택정리할 전화번호가 입력된 셀 주소입니다.
표시 형식선택표시할 전화번호 형식입니다. 11자리 기준 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
02구분 기호를 걷어내고 하이픈으로 맞추기
=TEXT ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( 전화번호, " ", "" ), "-", "" ), "(", "" ), ")", "" ) * 1, 표시 형식 )인수 설명 자세히 보기
인수구분설명
전화번호필수정리할 원본 번호입니다. SUBSTITUTE 함수를 네 번 겹쳐 공백·하이픈·여는 괄호·닫는 괄호를 차례로 빈 문자열로 바꿉니다. 바꿀 기호가 없는 셀은 그대로 지나가므로 이미 정리된 번호도 안전합니다.
표시 형식필수자리마다 0을 적고 하이픈을 끼워 넣습니다. 숫자가 형식보다 짧으면 앞자리를 0으로 채우므로 010의 맨 앞 0이 살아납니다.
예) "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
03국가번호가 붙은 번호까지 맞추기
=TEXT ( RIGHT ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( 전화번호, " ", "" ), "-", "" ), "+", "" ), 10 ) * 1, 표시 형식 )인수 설명 자세히 보기
인수구분설명
전화번호필수정리할 원본 번호입니다. SUBSTITUTE 함수로 공백·하이픈·더하기 기호를 걷어내면 +82로 시작하는 번호는 82가 앞에 남습니다.
10필수오른쪽에서 남길 글자 수이며 국내 번호에서는 10으로 고정입니다. 휴대폰 번호는 맨 앞 0을 뺀 10자리라 오른쪽 10자리만 남기면 국가번호 82가 떨어져 나갑니다.
표시 형식필수"000-0000-0000"처럼 적으면 잘라낸 10자리 앞에 0이 채워져 010으로 시작합니다.
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
이 공식이 사용하는 함수
동작 원리
01국가번호가 붙은 번호까지 맞출 때 (M365)
REGEXREPLACE 함수는 구분 기호를 지우고, RIGHT 함수는 마지막 10자리만 남깁니다. TEXT 함수가 이 숫자를 000-0000-0000 형식으로 표시합니다.
예제 시트에서 전화번호 형식을 통일하는 C2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
구분 기호를 제거합니다
공백·하이픈·플러스 기호와 일치하는 문자를 모두 빈 문자열로 교체합니다.
=REGEXREPLACE( 전화번호, "[ +-]", "" )
+82-10-2431-8890 적용 "821024318890"
기호를 모두 제거한 12자리 문자열입니다. 2
마지막 10자리를 잘라 숫자로 바꿉니다
오른쪽 10자를 남겨 국가번호를 제거하고 앞의 --가 텍스트를 숫자로 변환합니다.
=--RIGHT( "821024318890", 10 )
1024318890
국가번호를 제외한 10자리 숫자입니다. 3
형식을 입혀 표시합니다
표시 형식 000-0000-0000은 자리 수가 11개이므로 10자리 숫자 앞에 0을 채워 010-XXXX-XXXX로 만듭니다.
=TEXT( 1024318890, 표시 형식 )
"010-2431-8890"
앞에 0이 채워져 11자리 형식으로 출력됩니다. 03국가번호가 붙은 번호까지 맞추기
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
로그인 후 댓글을 작성할 수 있습니다.
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
