엑셀 전화번호 - 넣기 빼기, 엑셀 전화번호 0 입력 추가, 전화번호 서식

 

엑셀 전화번호 - 넣기 빼기, 엑셀 전화번호 0 입력 추가, 전화번호 서식

 

엑셀에서 전화번호 입력 시 하이픈(-)을 넣기도 하고, 하이픈(-)을 빼기도 하며, 전화번호 앞에 있는 0을 입력하거나 빼야 하는 경우도 종종 발생하지요.

이 경우 함수를 이용하는 방법도 있고 전화번호 서식을 이용하는 방법도 있습니다.
그 외에도 전화번호 중에서 지역번호 빼기나 넣기를 해야 하는 경우도 있지요.

 

이처럼 엑셀에서 전화번호 입력 시 발생하는 문제를 해결할 수 있는 다양한 방법에 대해 알아보도록 하겠습니다.

 

[엑셀 전화번호 - 빼기 사용방법]

 

엑셀 전화번호 - 빼기(LEFT, MID, RIGHT, IF 함수 사용 방법)

 

전화번호를 보면 크게 3가지로 구분이 되지요.

1. 국번이나 지역번호, 2. 중간번호, 3. 마지막 번호로 구성됩니다.
이 때 입력된 전화번호를 국번과 중간번호, 마지막번호로 구분해야 하는 경우도 발생하고, 중간의 - 표시인 하이픈만 제거해서 하이픈 없는 전화번호로 만들어야 하는 경우도 있습니다.
그런데 중간번호가 3자리인 경우도 있고, 4자리인 경우도 있기 때문에 2가지의 경우를 모두 계산할 수 있도록 함수를 작성해야 합니다.

 

첫 번째 예제는 중간번호가 3자리인 경우를 나타냅니다.
사용된 함수는 다음과 같습니다.

ㅇ 국번 : =LEFT(A2,3)
ㅇ 중간번호 : =MID(A2,5,3)
ㅇ 마지막번호 : =RIGHT(A2,4)
ㅇ 하이픈 없는 전화번호 : =LEFT(A2,3)&MID(A2,5,3)&RIGHT(A2,4)

 

ㅇ 국번 : LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A2 셀에서 앞 3글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A2 셀의 5번째부터 3글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 3자리이므로 3글자를 가져오는 것입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A2 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 없는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

 

두 번째 예제는 중간번호가 4자리인 경우를 나타냅니다.
사용된 함수는 다음과 같습니다.
ㅇ 국번 : =LEFT(A9,3)
ㅇ 중간번호 : =MID(A9,5,4)
ㅇ 마지막번호 : =RIGHT(A9,4)
ㅇ 하이픈 없는 전화번호 : =LEFT(A9,3)&MID(A9,5,4)&RIGHT(A9,4)

 

ㅇ 국번 : LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A9 셀에서 앞 3글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A9 셀의 5번째부터 4글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 4자리이므로 4글자를 가져오는 것입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A2 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 없는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

세 번째 예제는 중간번호가 3자리인 경우와 4자리인 경우를 모두 표시할 수 있는 계산식입니다.
사용된 함수는 다음과 같습니다.
ㅇ 국번 : =LEFT(A16,3)
ㅇ 중간번호 : =IF(LEN(A16)=12,MID(A16,5,3),MID(A16,5,4))
ㅇ 마지막번호 : =RIGHT(A16,4)
ㅇ 하이픈 없는 전화번호 : =LEFT(A16,3)&IF(LEN(A16)=12,MID(A16,5,3),MID(A16,5,4))&RIGHT(A16,4)

 

ㅇ 국번 : LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A16 셀에서 앞 3글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A16 셀의 5번째부터 3글자 또는 4글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 3자리인 경우와 4자리인 경우를 모두 처리해야 하므로 IF 함수를 사용했습니다. LEN 함수는 글자의 길이를 계산하는 함수로 중간번호가 3자리인 경우에는 전화번호 총 길이가 12이므로 IF 함수에서 LEN 함수를 사용하여 전화번호가 12글자인지를 체크해서 처리하는 경우입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A2 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 없는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

[엑셀 전화번호 - 넣기 함수 사용방법]

 

엑셀 전화번호 - 넣기(LEFT, MID, RIGHT 함수 사용 방법)

 

엑셀에서 전화번호를 입력하는 방법으로 1. 전화번호 중간의 마이너스(-) 기호를 넣어서 입력하는 방법과 2. 전화번호 중간의 마이너스(-) 기호를 넣지 않고 입력하는 방법이 있지요.

전화번호 중간에 마이너스(-) 혹은 하이픈(-)을 넣지 않고 입력할 경우에는 전화번호의 시작글자인 0 이 입력되지 않는 문제가 발생하지요.
하지만 맨 앞의 0 이 입력되지 않더라도 전화번호를 입력할 때는 0 없이 입력한 후 함수를 이용해서 중간의 하이픈과 0 표시를 넣을 수도 있지요.

위 그림의 예제를 보면서 설명을 드리겠습니다.

 

첫 번째 예제는 중간번호가 3자리인 경우를 나타냅니다.
사용된 함수는 다음과 같습니다.
ㅇ 국번 ="0"&LEFT(A2,2)
ㅇ 중간번호 =MID(A2,3,3)
ㅇ 마지막번호 =RIGHT(A2,4)
ㅇ 하이픈 있는 전화번호 ="0"&LEFT(A2,2)&"-"&MID(A2,3,3)&"-"&RIGHT(A2,4)

 

ㅇ 국번 : 입력한 전화번호에 0 이 포함되어 있지 않으므로 앞쪽에 “0” 을 추가해 주고, & 연산자를 이용해서 뒤쪽에 나오는 LEFT 함수결과와 연결시켜 주는 것입니다. LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A2 셀의 앞 2글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A2 셀의 3번째부터 3글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 3자리이므로 3글자를 가져오는 것입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A2 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 있는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

 

두 번째 예제는 중간번호가 4자리인 경우를 나타냅니다.
사용된 함수는 다음과 같습니다.
ㅇ 국번 ="0"&LEFT(A9,2)
ㅇ 중간번호 =MID(A9,3,4)
ㅇ 마지막번호 =RIGHT(A9,4)
ㅇ 하이픈 있는 전화번호 ="0"&LEFT(A9,2)&"-"&MID(A9,3,4)&"-"&RIGHT(A9,4)

 

ㅇ 국번 : 입력한 전화번호에 0 이 포함되어 있지 않으므로 앞쪽에 “0” 을 추가해 주고, & 연산자를 이용해서 뒤쪽에 나오는 LEFT 함수결과와 연결시켜 주는 것입니다. LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A9 셀의 앞 2글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A9 셀의 3번째부터 4글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 4자리이므로 4글자를 가져오는 것입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A9 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 있는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

세 번째 예제는 중간번호가 3자리인 경우와 4자리인 경우를 모두 표시할 수 있는 계산식입니다.
사용된 함수는 다음과 같습니다.
ㅇ 국번 ="0"&LEFT(A16,2)
ㅇ 중간번호 =IF(LEN(A16)=9,MID(A16,3,3),MID(A16,3,4))
ㅇ 마지막번호 =RIGHT(A16,4)
ㅇ 하이픈 있는 전화번호 ="0"&LEFT(A16,2)&"-"&IF(LEN(A16)=9,MID(A16,3,3),MID(A16,5,4))&"-"&RIGHT(A16,4)

 

ㅇ 국번 : 입력한 전화번호에 0 이 포함되어 있지 않으므로 앞쪽에 “0” 을 추가해 주고, & 연산자를 이용해서 뒤쪽에 나오는 LEFT 함수결과와 연결시켜 주는 것입니다. LEFT 함수는 문자열의 앞 몇 글자를 가져오는 함수로, A16 셀의 앞 2글자를 가져오는 경우입니다.
ㅇ 중간번호 : MID 함수는 문자열의 중간 몇 번째 위치부터 몇 글자를 가져오는 함수로, A16 셀의 3번째부터 3글자 또는 4글자를 가져오는 경우입니다. 이 예제에서는 중간번호가 3자리인 경우와 4자리인 경우를 모두 처리해야 하므로 IF 함수를 사용했습니다. LEN 함수는 글자의 길이를 계산하는 함수로 중간번호가 3자리인 경우에는 전화번호 총 길이가 9이므로 IF 함수에서 LEN 함수를 사용하여 전화번호가 9글자인지를 체크해서 처리하는 경우입니다.
ㅇ 마지막번호 : RIGHT 함수는 문자열의 마지막 몇 글자를 가져오는 함수로, A9 셀의 마지막 4글자를 가져오는 경우입니다.
ㅇ 하이픈 있는 전화번호 : 위의 3가지 함수를 함께 사용한 예제로, 함수를 통해 구한 값들을 연결해서 표시해 주기 위해 중간에 & 연산자를 포함시킨 것입니다.

 

[엑셀 전화번호 마이너스 넣기]

 

 

엑셀 전화번호 하이픈(-) 넣기(TEXT 함수 사용 방법)

 

엑셀에서 전화번호를 입력하다 보면 첫글자인 “0”이 사라지게 됩니다.
또는 0 이 없으며 중간에 하이픈(-)이 없는 엑셀 파일을 받은 경우 중간에 하이픈을 넣어 주어야 합니다.
이 문제를 해결하는 방법으로 TEXT 함수를 사용하는 방법도 쉬운 방법 중 하나입니다.

 

TEXT 함수의 또 다른 사용법은 아래의 링크를 참조하시기 바랍니다.

 

[엑셀정보] - TEXT 함수 사용법, 엑셀 금액 한글변환, 엑셀 금액 한자변환

 

[엑셀정보] - 엑셀 함수 배우기, 엑셀 유용한 팁 정리

 

TEXT 함수는 셀서식에서 지정하는 서식으로 참조하는 값을 표시해 주는 함수입니다.
A2 셀의 경우 0이 없고, 중간에 하이픈(-)이 없는 상태로 입력된 전화번호입니다.
이 전화번호의 중간에 하이픈을 넣어서 입력해 줄 때 TEXT 함수를 사용하면 쉽게 해결이 됩니다.

 

첫 번째 예제에 사용된 수식은 다음과 같습니다.
=TEXT(A2,"000-000-0000")

첫 번째 인수는 참조하는 값이나 셀의 위치로 A2 셀을 참조하고 있습니다.
두 번째 인수는 표시하고자 하는 셀서식으로 "000-000-0000" 형식으로 표시하겠다는 의미입니다. 전화번호의 중간번호가 3자리이므로 중간번호의 위치에 0을 3개만 입력한 경우입니다.

 

두 번째 예제에 사용된 수식은 다음과 같습니다.
=TEXT(A6,"000-0000-0000")

첫 번째 인수는 참조하는 값이나 셀의 위치로 A6 셀을 참조하고 있습니다.
두 번째 인수는 표시하고자 하는 셀서식으로 "000-0000-0000" 형식으로 표시하겠다는 의미입니다. 전화번호의 중간번호가 4자리이므로 중간번호의 위치에 0을 4개만 입력한 경우입니다.

 

세 번째 예제에 사용된 수식은 다음과 같습니다.
=TEXT(A10,IF(LEN(A10)=9,"000-000-0000","000-0000-0000"))

첫 번째 인수는 참조하는 값이나 셀의 위치로 A10 셀을 참조하고 있습니다.
두 번째 인수는 전화번호의 중간번호가 3자리인 경우와 4자리인 경우를 모두 표시해야 하므로 IF 함수와 LEN 함수를 함께 사용하여 처리하였습니다. LEN 함수는 문자열의 길이를 계산하는 함수로, 중간번호가 3자리인 경우에는 전화번호 총 길이가 9글자이므로, IF 함수에서 길이가 9인지를 체크하여 두 가지 경우를 모두 처리하도록 작성된 수식입니다.

 

[엑셀 전화번호 셀서식으로 하이픈 넣기]

 

엑셀 전화번호 하이픈(-) 넣기(셀서식 사용 방법)

 

전화번호에 0과 하이픈이 없는 전화번호를 0과 하이픈이 포함된 전화번호로 변경하는 방법으로 셀서식을 활용하는 방법이 있습니다.

 

첫 번째 예제에 사용된 셀서식은 다음과 같습니다.

[<=999999999]0##-###-####;0##-####-####

 

첫 번째 예제는 우리가 흔히 사용하는 전화번호 형식으로 중간에 하이픈(-)이 2회 들어가는 경우입니다. 입력된 전화번호의 자리수가 9자리인 경우와 10자리인 경우를 모두 처리해야 하므로 대괄호[] 안에 조건을 입력한 예제입니다. 입력된 조건은 값이 999999999 보다 작거나 같은 경우 중간번호를 3자리로 표시하고, 값이 999999999 보다 클 경우에는 중간번호를 4자리로 표시하는 셀서식입니다.

 

두 번째 예제에 사용된 셀서식은 다음과 같습니다.

[<=999999999](0##)###-####;(0##)####-####

 

두 번째 예제는 국번의 좌우에 괄호를 씌워주는 경우입니다. 셀서식 사용방법은 첫 번째 예제와 유사하며, 국번의 좌우에 괄호를 넣어주었다는 것입니다. 이처럼 원하는 서식이 조금 다른 경우에는 필요한 부분을 수정해서 사용하시면 되겠습니다.

 

[엑셀 전화번호 SUBSTITUTE 함수 활용 방법]

 

엑셀 전화번호 - 빼기(SUBSTITUTE 함수 사용 방법)

 

엑셀에서 입력된 전화번호의 중간 하이픈을 제거하는 방법으로 SUBSTITUTE 함수를 사용하는 방법이 있습니다.

SUBSTITUTE 함수는 텍스트 중의 “이전 텍스트”를 “이후 텍스트”로 변경해 주는 함수입니다. 이 예제에서는 전화번호 중간에 입력된 하이픈을 공백으로 변경해 주는데 사용할 수 있습니다.

 

예제에 사용된 함수는 다음과 같습니다.

=SUBSTITUTE(A2,"-","")

 

[엑셀 SUBSTITUTE 함수]

 

SUBSTITUTE 함수의 사용방법은 다음과 같습니다.

 

첫 번째 인수는 값이나 참조할 셀을 입력합니다.

 

두 번째 인수는 바꾸고자 하는 이전 텍스트를 입력합니다. 텍스트의 양쪽에는 위 그림처럼 따옴표(“”)로 감싸 주어야 합니다. 바꾸고자 하는 텍스트가 하이픈(-) 이므로 “-”을 입력합니다.

 

세 번째 인수는 바뀔 이후 텍스트를 입력합니다. 이번 예제에서는 하이픈을 제거해야 하므로 공백으로 입력합니다. 공백으로 입력하는 방법은 따옴표(“”) 사이를 띄지 않고 “”로 입력하면 됩니다.

 

네 번째 인수는 생략이 가능하므로 입력하지 않겠습니다.

이처럼 함수를 입력하게 되면 전화번호 중간의 마이너스(-)를 제거할 수 있습니다.

 

[엑셀 전화번호 직접 입력방법]

 

전화번호에 0을 넣어서 입력하는 방법

 

엑셀에서 전화번호를 입력하면 첫 글자인 0 이 사라지는 문제가 발생합니다. 이외에도 엑셀에서는 첫 글자로 같다의 의미인 “=”이나 마이너스(-) 를 첫 글자로 입력할 때도 문제가 발생합니다. 이처럼 원하는 글자로 입력이 되지 않을 경우에 해결할 수 있는 방법이 싱글 쿼테이션을 입력한 다음 나머지 내용을 입력하는 것입니다.

 

위의 예제처럼 첫 글자로 ‘를 입력 후 전화번호를 입력하면 첫 글자인 0을 포함해서 입력할 수 있습니다.

싱글 쿼테이션은 키보드에서 엔터키 바로 왼쪽 따옴표와 같은 키에 배치되어 있습니다. 따옴표를 입력할 때는 SHIFT 키를 누르고 입력하고 싱글 쿼테이션을 입력할 때는 SHIFT 키를 누르지 않고 바로 입력하면 됩니다.

 

위의 예제를 자세히 살펴보고자 하시는 분은 아래의 엑셀 파일을 다운받아 살펴보시기 바랍니다.

 

 

전화번호 - 넣기 빼기.xlsx

 

 

이 외에도 엑셀의 다양한 함수 사용법이나 유용한 팁은 아래의 글을 참조하시기 바랍니다.

 

 

유용한 엑셀 팁, 엑셀 함수 정리   [엑셀정보] - 엑셀 함수 정리, 엑셀 유용한 팁 정리