전화번호를 입력하기 위한 표시 형식에 관하여

안녕하세요.

 

유튜브 구독자 정*영님이 메일로 질문을 주셨습니다.

 

안녕하세요 유튜브 강좌보다가 메일이 있어 이리로 질문 드려봅니다.
(안되는거면 무시하셔도 됩니다)
휴대폰번호 표시하기 위해 서식을 010-####-####으로 지정했는데 지정이 되지 않습니다.
데이터에는 ! 표시 나타나면서 텍스트형식으로 저장된 숫자, 오류 무시 등 리스트가 나옵니다.
어떤 오류가 있는것인지, 어떻게 해야하는지 모르겠어서 질문드립니다.
또 이미 데이터를 010-####-####, 010########, ########(010없이) 등 입력했을때
서식을 010-####-####으로 지정하면 모든 데이터가 동일한 서식으로 지정되는지도 궁금합니다.
감사합니다.

데이터 입력하기와 데이터 표시형식 기능과 관련된 문제인데, 진작부터 소개를 해야겠다고 벼르고 있던 부분을 꼭 찝어서 질문해 주셔서 여러분에게 소개를 드리겠습니다.

 

완성 파일 다운로드

전화번호입력_완성파일.xlsx

0.01MB

 

전화번호를 입력하는 방법이 여러 가지 있습니다.

 

예를 들어

 

010-1234-1234

01012341234

02-1234-1234

0212341234

051-123-1234

 

등의 형태입니다.

 

표시 형식 기능을 이용해서 위의 형태로 입력된 내용을 모두 맞게 표현할 수 없습니다.

 

질문하신 내용 중 언급하신 010-####-#### 형식은 표시 형식으로 지정한다는 것은 숫자 형식으로 국번을 제외한 8자리를 입력했을 때 제대로 표시되는 형식입니다.

 

가령 전화번호 010-1234-5678인 경우 010-####-#### 형식인 경우 입력할 때는 12345678을 써야 합니다.

그러면 010-1234-5678로 표시됩니다.

 

그런데 이런 표시 형식을 적용하면 010이 아닌 번호인 경우엔 제대로 표현되지 않는 문제가 있습니다.

 

전화번호가 011-1234-5678인 경우 010-####-#### 형식을 표시 형식으로 지정한 경우엔 01112345678로 입력하면 111-1234-5678로 표시됩니다.

 

입력하려는 모든 전화번호가 010으로 시작하고 4자리 국번, 4자리 전화번호인 경우라면 010-####-#### 형식으로 표시 형식을 지정해 두고 입력할 때는 4자리 국번과 4자리 전화번호를 숫자 형식으로 입력하는 것이 가장 편한 방법이긴 합니다.

 

전화번호 010-1234-5678인 경우 12345678로 입력하면 010-1234-5678로 표시되는 겁니다.

 

보통의 경우엔 전화번호 입력 형식이 다양합니다.

그래서 전화번호는 사용자가 직접 010-1234-5678 형식으로 입력하는 것이 관리하기 좋습니다.

 

전화번호 13글자를 모두 입력해야 한다는 불편한 점은 있지만 가장 입력이 간단하고 보기 좋습니다.

 

'전화번호를 표시 형식으로 편하게 입력해 보자'고 하는 것은 오히려 장래에 불편함을 겪게 되기 십상입니다.

 

---

 

직접 입력하기로 결정한 뒤에도 문제는 질문에서처럼 이미 입력된 전화번호들입니다.

 

010-####-#### 형태로 입력된 것은 상관 없습니다.

010######## 형태나 010을 제외하고 ######## 형태로 입력된 것들은 변환 작업이 필요합니다.

 

몇가지 예시를 만들었습니다.

 

[B4] 셀은 우리가 원하는 형식입니다.

[B5] 셀은 01012345678 전화번호를 바로 입력하면 셀 서식이 일반 형식이기 때문에 숫자로서 의미없는 앞자리 0을 없애고 나타낸 모양입니다.

[B6] 셀은 숫자로 010을 제외한 나머지를 입력한 모양입니다.

[B7] 셀은 셀서식을 텍스트 형식으로 바꾸고 01012345678을 입력한 모양입니다.

우리가 원하는 표시 형식은 문자가 13개입니다. 

그 외 나머지는 문자가 10개, 8개, 11개 입니다.

 

이 값을 이용해서 IF, LEN, CONCATENATE, LEFT, RIGHT, MID 함수와 문자열 연결 연산자 &(앰퍼샌드)를 이용해 수식을 만들어 보겠습니다.

 

한꺼번에 모든 경우를 수식으로 나타내면 너무 복잡해지기 때문에 먼저 [C5] 셀에 문자가 10자리인 경우만 따로 만들겠습니다.

 

[C5] 셀에 커서를 두고 =if를 입력해 나타나는 함수목록에서 IF 함수를 더블클릭합니다.

 

단축키 <Shift + F3>을 누르거나 수식 입력줄 fx를 눌러 함수 인수 대화상자를 나타냅니다.

Logical_test 인수에 len(B5)=10으로 입력해 문자가 10글자인 경우로 조건을 지정하고, Value_if_true 인수에는 문자열 합치기 함수인 CONCATENATE 함수명과 괄호를 직접 입력해 concatenate( )로 나타냅니다.

수식입력줄의 concatenate 글자 부분을 마우스 왼쪽으로 클릭하면 함수 인수 대화상자에 CONCATENATE 함수 인수가 표시됩니다.

Text1 인수에 0을 입력합니다.

Text2 인수에 left(B5,2)를 입력합니다.

Text3 인수에 -를 입력합니다.

Text4 인수에 mid(B5,3,4)를 입력합니다.

Text5 인수에 -를 입력합니다.

Text6 인수에 right(B5,4)를 입력합니다.

[확인]을 눌러 수식을 완성합니다.

[B5] 셀에 입력된 내용이 우리가 원하는 모양으로 변환되어 [C5] 셀에 나타났습니다.

 

---

 

계속 이어서 [B6] 셀에 입력된 내용을 변환하는 수식을 [C5] 셀 수식을 수정해 추가하겠습니다.

 

[C5] 셀에 커서를 두고 단축키 <Shift+F3>을 눌러 수식을 수정합니다.

Value_if_false 인수에 커서를 두고 if( )를 입력합니다.

수식 입력줄 끝에 있는 if( ) 부분 중 if 글자에 마우스 왼쪽을 누르면 함수 인수 대화상자가 두번째 IF 함수 인수창으로 바뀝니다.

Logical_test 인수에 len(B5)=8을 입력합니다.

Value_if_true 인수에 concatenate( )를 입력합니다.

 

수식표시줄에서 수식 끝부분 concatenate( )를 마우스 왼쪽 단추 클릭해 함수 인수 대화상자를 CONCATENATE 함수 인수 창으로 바꿉니다.

Text1 인수에 010-를 입력합니다.

Text2 인수에 left(B5,4)를 입력합니다.

Text3 인수에 -를 입력합니다.

Text4 인수에 right(B5,4)를 입력합니다.

문자가 8개인 경우에 대한 수식 작업을 완성했습니다.

 

이어서 문자가 11개인 경우에 대한 수식 작업을 하기 위해 수식 입력줄 끝 부분 IF를 마우스 왼쪽단추로 클릭합니다.

Value_if_false 인수에 if( )를 입력합니다.

 

수식 입력줄에서 방금 입력한 if( ) 글자를 클릭해야 하는데, 작성된 수식이 너무 길어서 글자가 보이지 않습니다.

수식 입력줄 오른쪽 끝 수식 입력줄 확장 단추를 눌러 수식 전체를 표시합니다.

수식 입력줄 끝부분 if를 마우스 왼쪽 단추로 클릭해 IF 함수 인수창을 나타냅니다.

Logical_test 인수에 len(B5)=11을 입력합니다.

Value_if_true 인수에 concatenate( )를 입력합니다.

수식 입력줄에서 끝부분 concatenate( )를 마우스 왼쪽 단추로 눌러 CONCATENATE 함수 인수창을 나타냅니다.

Text1 인수에 left(B5,3)을 입력합니다.

Text2 인수에 -를 입력합니다.

Text3 인수에 mid(B5,4,4)를 입력합니다.

Text4 인수에 -를 입력합니다.

Text5 인수에 right(B5,4)를 입력합니다.

다시 수식 입력줄 수식 중 3번째 IF를 마우스 왼쪽 단추를 클릭해 IF 함수 인수창으로 돌아갑니다.

Value_if_false 인수에 B5를 입력합니다.

 

문자가 10개, 8개, 11개 어느 것 하나에도 해당되지 않는 경우엔 입력된 내용을 그대로 나타냅니다.

[확인]을 눌러 수식을 완성합니다.

[C7] 셀까지 채우기를 해서 수식이 제대로 동작하는지 확인합니다.

 

필요하신 분께 도움되길 바랍니다.

수고하셨습니다.