엑셀 핸드폰 번호 “-” 하이픈으로 분리해서 표시하는 방법

엑셀 핸드폰 번호 “-” 하이픈으로 분리해서 표시하는 방법

 

환경:  Microsoft Excel 2010

 

전화번호가 하이픈 없이 전부 붙어 있을 때 지역 번호, 번호 길이 등에 상관없이 하이픈을 넣어서 구분하는 방법에 대해 알아 보겠습니다. 모든 전화번호 구분 규칙을 IF, LEFT, RIGHT, MID 함수로 만들어 보겠습니다.

 

▼ 함수 구현 내용이 길어서 규칙 별로 구분하였습니다. 아래 구분대로 수식을 구성한 후 하나로 합치겠습니다.

 

1. 010 일때 (휴대폰 앞자리는 010 으로 통일되었죠)

2. 지역번호인데 2자리일 때 (02 서울)

3. 지역번호인데 3자리일 때 (02 외 )

 

먼저 1번에 해당하는 010 일 때 입니다. IF 를 사용해서 앞자리를 3개를 자른 후 (LEFT(B2,3)) 010 인지 비교하고 맞는다면 LEFT(B2,3)&"-"&MID(B2,4,LEN(B2)-7)&"-"&RIGHT(B2,4) 를 수행합니다. 설명하자면 앞자리 3자리 자르고 하이픈으로 연결합니다. 중간 번호는 MID 함수를 사용해서 4자리부터 전체 길이에서 7 을 뺀 나머지 만큼 자릅니다. 그러니까 전체 길이가 11자리인 번호에 7 을 빼면 4 이 나오겠죠. 그 값이 중간 번호 입니다. 마지막 번호는 RIGHT 함수로 오른쪽에서 개수를 세는 것이죠.

 

IF(LEFT(B2,3)="010", LEFT(B2,3)&"-"&MID(B2,4,LEN(B2)-7)&"-"&RIGHT(B2,4),"")

 

 

 

▼ 두 번째 규칙인 010 이 아니고 02 일 때 입니다. IF 에서 수식이 들어가는 부분은 거짓이기 때문에 3번째 인수로 들어가겠죠. 인수로 들어간 수식은 02 일 때 첫 번째 규칙에서 설명한 수식이 들어갑니다. 차이점은 중간에 들어가는 숫자 값을 구할 때 7 이 아닌 6을 넣습니다.

 

IF(LEFT(B15,3)="010","", IF(LEFT(B15,2)="02", LEFT(B15,2)&"-"&MID(B15,3,LEN(B15)-6)&"-"&RIGHT(B15,4),""))

 

 

▼ 세 번째 규칙은 010 이 아니며 02 가 아닐 때 입니다. 02 를 제외한 모든 지역 번호가 되겠죠. 수식은 첫 번째 설명한 것과 동일합니다.

 

IF(LEFT(B16,3)="010","", IF(LEFT(B16,2)="02","", LEFT(B16,3)&"-"&MID(B16,3,LEN(B16)-7)&"-"&RIGHT(B16,4)))

 

 

▼ 이렇게 3개의 규칙을 부분적으로 구현한 소스를 전부 합쳐서 하나로 만들었습니다. 한번에 안보이므로 수식 입력 창을 마우스로 끌어 내려서 키웁니다. 이제 모든 전화번호 경우의 수를 해결할 수 있는 수식이 완료 되었습니다. 이렇게 IF 를 사용하면 어떤 경우의 수도 전부 해결이 가능합니다.

 

IF(LEFT(B14,3)="010", LEFT(B14,3)&"-"&MID(B14,4,LEN(B14)-7)&"-"&RIGHT(B14,4), IF(LEFT(B14,2)="02", LEFT(B14,2)&"-"&MID(B14,3,LEN(B14)-6)&"-"&RIGHT(B14,4), LEFT(B14,3)&"-"&MID(B14,3,LEN(B14)-7)&"-"&RIGHT(B14,4)))