엑셀에서 전화번호를 입력할 때 하이픈(-)을 빼고 숫자만 입력해도 자동으로 하이픈(-)이 적절한 위치에 붙도록 하면 편리하겠죠? 사용자 서식을 지정하면 가능합니다.
하지만 문제는 다양한 형식의 전화번호(집전화번호, 휴대폰 전화번호, 등등)를 짬뽕해서 입력하는 경우에 하이픈(-)을 적절하게 넣도록 서식을 만드는 것이 까다롭다는 것입니다.
예를들어 010으로 시작하는 휴대폰 번호는 (-)하이픈으로 구분된 중간 번호의 갯수가 3개 일수도, 4개 일수도 있습니다. 또 다른 예로 02국번을 가진 전화번호와 031국번을 가진 전화번호가 섞여있을 때 하이픈(-)을 넣는 것 역시 골치아픕니다. 게다가 중간 번호의 개수가 3개 또는 4개이므로 더 복잡해지죠. 이 경우는 제가 아는 범위에서는 사용자 지정서식만으로는 해결할 수 없네요.
여기에서는 이런 문제를 통합적으로 해결하는 방법을 소개하는데 조금이라도 도움이 되었으면 좋겠습니다.
인터넷 상에 있는 정보들은 모두 엑셀에서 핸드폰 번호를 입력하는 내용에 대한 것만 다루고 있는데 국번이 다른 집 전화 번호들을 섞어 입력할 경우에 대한 내용은 찾을 수 없었습니다.(세상에 엑셀 전문가들이 많을 텐데 왜 없을지 의문이 드네요. 저는 전혀 엑셀 전문가는 아니고 취미로 할 뿐이며 게임 프로그래머일 뿐입니다.)
물론 여기에서 소개하는 방법은 간단한 것은 아니고 어려운 내용일 수 있습니다. 만약 핸드폰 번호와 같은 형태의 전화번호만 다룬다면 다음에 소개하는 일반적인 방법으로도 충분합니다.
생각보다 내용이 많아져서 글은 1부 2부로 나누어 씁니다. 이 글에서는 일반적인 해결책을 먼저 살펴본 후에 다음 글에서 필자의 방법을 소개하겠습니다.
같은 종류의 핸드폰 번호만 입력하는 경우
예전에는 핸드폰 번호가 017, 016과 같은 번호가 있었지만 요새는 대부분 010으로 시작하죠. 우선 011, 017과 같은 번호가 섞여있는 경우에 대한 것은 쭉 아래로 내려가서 조건부 서식을 사용하는 방법을 참고하고 여기서는 010로 시작하는 전화번호인 경우에 대해서 설명하겠습니다.
이 문제의 해결은 사용자 지정 서식에 들어가서 숫자 서식 조건 구문을 사용하면 됩니다. 뭔가 이름이 복잡하죠? 어려운 건 아니니 이것에 대해 모른다면 10분만 투자해서 다음 글을 읽어보시길 바랍니다. (※ 읽기 귀찮으시면 아래 그림 설명만 보고 따라만 하셔도 됩니다.)
셀에 서식 지정없이 01012345678 을 입력하면 숫자로 인식되어 위 그림처럼 맨 앞에 '0'이 없어지게 됩니다. 게다가 핸드폰 번호들의 형식이 중간번호가 3자리인 것과 4자리 인것이 섞여 있군요.
이건 다음과 같이 사용자지정서식을 적용하면 해결됩니다.
[>999999999]0##-####-####;0##-###-####
설명 : 핸드폰 번호 010-123-4567 과 010-1234-5678 를 하이픈(-)을 제외하고 셀에 입력하면 숫자로 인식되어 다음과 같이 0이 제외되어 표시됩니다.
101234567
1012345678
각각 9자리수 숫자와 10자리수 숫자로 입력됩니다.
9자리수 숫자는 핸든폰 번호의 중간 번호가 3개일 때고
10자리수 숫자는 중간 번호가 4개일 때 입니다.
9자리 수와 10자리수를 구별하는 것은 결국 핸드폰 번호 중간번호가 3개인 경우와 4개인 경우를 구별하는 것입니다.
※ 9자리수 숫자와 10자리수 숫자를 구별하는 법
9자리수 숫자의 제일 큰 숫자(맥시멈)는 9가 9개인 수입니다.(999999999)
따라서 999999999 보다 큰 수는 10자리 이상입니다.
[>999999999] 는 10자리 이상의 숫자를 의미하는 조건입니다. 이 조건에 맞으면 서식 0##-####-#### 가 적용되고 나머지 경우는 서식 0##-###-#### 이 적용됩니다.
텍스트 형식으로 입력된 전화번호에서 (-)하이픈 없앤 후 다시 전화번호 서식 적용하는 방법
애초에 앞에서 소개한 방법으로 서식을 제대로 적용해 놓았으면 이 방법은 필요없습니다. 그러나 세상일이 내뜻 같지 않아서 이런 방법을 모르고서 전화번호 앞에 0을 표시하려고 셀 서식을 텍스트로 해 놓을 수 있습니다.(이런 경우 아래 그림처럼 셀 왼쪽위에 표시가 생긴다.) 이럴 때는 전화번호를 숫자 데이터로 바꿔준 후에 앞서 소개한 방법을 적용하면 됩니다.
※ 참 고
셀 왼쪽 위에 표시는 셀 서식은 텍스트, 셀의 내용은 숫자로만 구성되어 있는 경우에 나타납니다. 이 표시가 있으면 서식을 적용하거나 나중에 조건부 서식을 적용하는데 문제가 발생하므로 숫자형식으로 변환해줍니다.
간혹 대량으로 이런 표시가 되어 있을 경우가 있는데 하나하나 바꾸지 말고 위 그림처럼 전체를 선택한 후에 한번에 변환할 수 있습니다. (범위를를 선택할 때는 마우스로 처음 선택하는 셀이 표시가 있는 셀이어야 노란 바탕 느낌표가 뜹니다.)
하지만 -(하이픈)이 포함된 경우는 바로 숫자로 못바꿉니다. 이 때는 따로 표를 만들어 다음과 같은 방법으로 모두 숫자형식으로 바꿔줍니다.
이제 숫자형식으로 변경된 전화번호에 앞서 방법대로([>999999999]0##-####-####;0##-###-####) 서식 지정 하면 됩니다.
(숫자형식으로 변경된 전화번호를 원래의 위치에 복사하려면 값만 선택하여 붙여넣기를 해주시면 됩니다. )
--SUBSTITUTE(B3,"-","")
SUBSTITUTE 함수는 첫번째 인자(B3)에서 두 번째 인자("-")를 찾아 세 번째 인자""로 대체합니다. 그리고 결과를 텍스트로 반환합니다.
위 식은 - 를 찾아서 없앤 후에 텍스트로 반환합니다. 그리고 앞에 -- 는 -1을 두 번 곱한 것입니다.
텍스트와 숫자를 연산을 하면 숫자형식으로 변형되는 원리를 이용한 것으로 -1을 두번 곱한 것은 1을 곱한 것과 같습니다.
조건부 서식으로 여러 국번의 전화번호 입력에 대해 전화번호 서식 제대로 지정하기
집 전화번호처럼 국번이 다양한 경우에도 전화번호의 중간번호가 3자리인 경우, 4자리인 경우로 나뉩니다. 앞서 방법처럼 사용자 지정 서식에 조건을 넣는 것은 제한이 있기 때문에 복합적인 조건에 대해서는 다른 추가적인 방법이 필요합니다.
조건부 서식을 추가적으로 사용하면 하나의 셀에 대해서 제한없이 조건을 추가할 수 있으므로 이를 해결할 수 있습니다. 물론 조건이 여러개 필요하므로 손은 많이 가지만 한번만 적용하면 모든 전화번호에 대해서 알아서 서식이 적용되도록 할 수 있습니다.
- 010으로 시작하는 번호에 대한 조건 지정
아래 그림은 010, 02, 031, ... 등으로 시작하는 전화번호가 짬뽕되어 입력되어 있습니다. 우선 010으로 입력된 경우에 대해 선택적으로 해결하겠습니다.
숫자로 바꿀 수 있는 형식이 포함되어 있다면 아래 그림처럼 반드시 숫자로 변환해 주고 시작합니다.
=LEFT(--SUBSTITUTE(B3,"-",""),2)="10"
B3은 선택한 범위에서 가장 첫 번째 셀이다.
해설
셀 내용이 010-123-4567 라면 SUBSTITUTE 함수로 하이픈(-) 모두 제거한 후 --를 곱해서 숫자로 만든다.(시작 숫자 0 제거) 이 값은 LEFT 함수의 첫 번째 인수로 쓰인다.
LEFT( 101234567, 2 ) 은 첫 번째 인자에서 가장 왼쪽에 2 개의 문자를 반환해 준다. 반환값은 "10"이 되고 결국 비교식 ="10" 에 의해서 TRUE를 반환한다.
TRUE를 반환하면 서식이 적용된다.(서식 만드는 법은 다음 그림 참고)
조건부 서식이 제대로 적용되는지 알아보기 위해서 채우기에서 셀의 색도 선택해 보습니다.
이제 모두 확인을 눌러 창을 다 닫아보겠습니다.
다음과 같이 표시되면 성공입니다.
사실 수많은 국번에 대해서 조건부 서식 하나하나를 다 적용할 수는 있지만 힘듭니다.
우선 앞서 방법과 좀 다른 방식의 접근 방법이 필요한데 할 이야기도 많고 체력적으로 글쓰는게 너무힘드네요.ㅠ 좀 길어져서 모든 국번에 대해서 적용되게 하는 방법에 대한 내용은 다음 포스팅에 정리하겠습니다.