지난 포스팅에서 좀 길게 설명한 것 같습니다. 간단히 요약하면 사용자 지정 서식에
[>999999999]0##-####-####;0##-###-####
이렇게 입력하면 010, 017, 등과 같은 휴대폰 번호에 대해서는 제대로 적용되지만 지역번호가 있는 일반 전화에 대해서는 사용할 수 없습니다.
국번은 02, 031, 051 등 수십가지가 있기 때문에 최대 3가지 조건만 허용하는 사용자 지정 서식으로는 해결이 안되는 것입니다.
이 문제의 해결책은 두 가지가 있는데 수식을 이용하는 방법과 필자가 잔머리를 굴려서 사용하는 방법인 조건부 서식을 이용하는 방법이 있습니다.
수식을 이용하는 방법은 단 한줄이면 되지만 단점은 전화번호를 입력하는 셀과 출력되는 셀을 따로 만들어야 한다는 것입니다. 조건부 서식을 이용하는 방법은 약간 수고스럽긴 한데 입력셀에 바로 서식이 적용이 되니 편리하고 확장도 가능합니다.
수식을 이용하는 방법
- 전화번호 앞에 0이 없는 형식으로 입력된 경우(숫자 형식으로 입력되었을 때)
전화번호 출력 셀에 다음과 같은 수식을 적용해보도록 하겠습니다.
=0&LEFT(B3,2-(LEFT(B3,1)="2")) & TEXT(MID(B3, 3-(LEFT(B3,1)="2"),9), "-#-0000")
위 수식을 해석해 드리고 싶지만 글이 길어질 것 같아서 해석은 패스하겠습니다. 단 전화번호가 텍스트 형태로 입력되어 앞에 0이 붙은 경우는 다음 방법과 같이 하면 됩니다.
- 텍스트 형식으로 입력된 전화번호
=LEFT(B3,3-(LEFT(B3,2)="02")) & TEXT(MID(B3, 4-(LEFT(B3,2)="02"),9), "-#-0000")
기존 수식의 맨앞에 0&를 빼고 붉은 표시 숫자에 1을 더해서 수정했습니다. 수식에 대한 자세한 설명은 생략합니다. 수식에 관심있으시면 어렵지 않게 분석하실 수 있을 겁니다.
앞에서도 언급했듯이 위 방법의 단점은 입력셀과 출력셀을 따로 두어야한다는 것입니다. 하지만 매우 간편하죠.
조건부 서식을 지정하여 전화번호 서식 만들기
(이 내용은 숫자형식으로 입력한 전화번호에 대한 것입니다. 텍스트 형식으로 입력되었다면 숫자 형식으로 바꿔서 0을 없앤 후에 적용하시길 바랍니다.)
다음은 조건부 서식으로 전화번호 서식을 적용한 화면입니다. 채우기 서식으로 서울 전화번호와 다른 국번을 가진 번호를 구분해 주었습니다.
① 서식을 적용할 셀 범위를 선택한 후 조건부 서식의 새규칙을 클릭합니다.
② 다음 수식을 입력 입력합니다. B3는 선택한 셀 범위에서 가장 첫 번째 셀의 주소입니다.
=LEFT(B3,1)="2"
입력된 전화번호가 2로 시작되면 TRUE을 반환 하고 서식이 적용된다. (서울 국번)
③ 적용될 서식을 지정합니다. 위 그림에서 빨간 화살표 표시된 서식 버튼을 눌러줍니다.
채우기 서식을 적용해 봤습니다.
그리고 표시 형식에서 사용지 저정형식을 다음과 같이 적용합니다.
[>99999999]0#-####-####;0#-###-####
02로 시작하는 전화번호는 중간번호가 3개인 경우 8자리수, 중간번호가 4개면 9자리 수가 된다.
위 코드는 이 둘을 구분해서 서식을 적용하는 서식코드다.
이제 모두 확인을 눌러준 후에 서식이 제대로 적용됐는지 확인해 보겠습니다.
다른 국번을 가진 번호에 대해서는 따로 작업을 해야합니다.
④ 앞에서와 같은 방법으로 조건부 서식에서 새규칙을 만들되 적용할 수식, 사용자서식은 다음과 같습니다.
수식
=LEFT(B3,1)<>"2"
2로 시작하지 않을 때 참(TRUE)
사용자지정서식
[>999999999]0##-####-####;0##-###-####
02로 시작하지 않는 전화번호는 중간번호가 3개인 경우 9자리수, 중간번호가 4개면 10자리 수가 된다.
이런 식으로 조건을 늘려갈 수 있습니다. 단 주의할 점은 조건부 서식은 우선순위를 가지고 있습니다. 예를들어 전화번호가 2로 시작하지 않는 경우에 대한 조건을 정했는데 여기에 (10)으로 시작하는 번호에 대한 조건부 서식을 따로 정하고 싶다면 10으로 시작하는 번호에 대한 서식이 우선순위가 높아야 합니다. 왜냐면 10으로 시작하는 경우 위 두 조건 모두 만족하기 때문입니다.
방법은 홈 -> 조건부 서식 -> 규칙관리에 들어가서
만들어진 규칙의 우선순위를 조정할 수 있습니다.