엑셀 전화번호 하이픈(대시) 넣기

오픈 API로 아파트 단지 코드를 받아 엑셀로 저장하고 보니 아파트 관리사무소 연락처와 팩스번호가 있었다. 그런데 대시(하이픈)가 없는 전화번호여서 중간에 하이픈(대시) 넣기를 해보고 그 결과를 포스팅해본다.

 

 

엑셀 데이터에서 아파트 관리사무소 전화번호에 하이픈(대시) 넣기

우리나라의 유선 전화번호는 KT 114에서 관리하고 있는데, 그 구조는 "지역번호-국번-전화번호"로 되어있다.

 

지역별 지역번호

02	031	032	033	041	042	043	044	051	052	052	054	055	064
서울	경기	인천	강원	충남	대전	충북	세종	부산	울산	대구	경북	경남	제주

• 서울은 2자리, 나머지 지역은 3자리
• 특수번호(지능망)는 070, 080 등 3자리도 있고 1588, 0502 등 4자리도 있지만, 여기서는 4자리 번호는 제외(함수를 응용하면 쉽게 할 수 있기 때문임)
• 국번은 서울은 3, 4자리이고, 나머지 지역은 3자리(특수번호도 3, 4자리임)
• 전화번호는 모두 4자리로 되어있음

아파트 관리사무소 연락처

위 이미지에서 A열 "관리사무소 연락처"와 D열 "관리사무소 팩스"를 보면 하이픈이 없는 번호로 되어있다.

이 번호에 대해 C열과 F열처럼 하이픈을 넣어서 알아보기 편한 번호로 만들건데,

이때 사용하는 엑셀의 함수는  IF, LEFT, LEN, MID, AND 등이다.

 

전화번호 분리 방법은

IF 함수로 서울과 기타 지역으로 조건 분기를 하는데, LEFT 함수로 번호(숫자) 2개를 추출해서 값이 "02"면 LEFT 함수로 2자리까지 추출하고, 값이 "02"가 아니면 3자리까지 추출한다.(B열, E열 지역번호)

=IF(LEFT(A2,2)="02",LEFT(A2,2),LEFT(A2,3))

 

IF 함수를 이용해서 서울인 경우에 해당 지역번호가 "02"이고 A열(또는 D열)의 길이가 각각 9 또는 10일 경우(LEN함수 이용), MID함수를 이용해서 중간의 국번(3 또는 4자리)을 추출하고,

RIGHT 함수를 이용해서 끝에서 4자리까지의 전화번호를 추출한다.

=IF(AND(B2="02",LEN(A2)=9),B2&"-"&MID(A2,3,3)&"-"&RIGHT(A2,4),IF(AND(B2="02",LEN(A2)=10),B2&"-"&MID(A2,3,4)&"-"&RIGHT(A2,4),IF(AND(LEN(B2)=3,LEN(A2)=10),B2&"-"&MID(A2,4,3)&"-"&RIGHT(A2,4),IF(AND(LEN(B2)=3,LEN(A2)=11),B2&"-"&MID(A2,4,4)&"-"&RIGHT(A2,4),""))))

서울 지역 이외에 대해서 유사한 방법으로 추출할 수 있는데,

지역번호의 길이(LEN)가 3이고 A열(또는 D열)의 길이가 각각 10 또는 11일 경우, MID함수를 이용해서 국번(3 또는 4자리)을 추출하고,

RIGHT 함수를 이용해서 동일하게 끝에서 4자리까지의 전화번호를 추출한다.

 

휴대폰 번호도 위 함수를 이용하는 방식으로 하면 쉽게 하이픈(대시)를 삽입할 수 있다.