엑셀 휴대폰번호 핸드폰번호 전화번호 서식 - (하이폰) 넣기 방법 text함수 사용법

엑셀에서 관리나 기타  업무시 전화번호 관련 데이터들이 있는데,

이 휴대폰 번호및 일반전화번호들이 그냥 숫자들만 있어서 보기가 어려운 경우들이 종종 발생을 한다.

"01022223333" , "0113335555"  "021112222", "05144455555" 이런게 있으면 보기가 좀 짜증이 나고 가끔마다 옛날번호도 있어 자리수까지 헷갈린다. 이련경우 휴대폰번호나 전화번호에 -(하이픈)을 번호 형식에 맞게 표기를 해주고자 할때

몇가지 방법중 간단한게

엑셀 Text()함수로 처리가 가능하다.

 

TEXT 함수

text함수를 사용하면 숫자에 서식코드를 이용해서 숫자에 보고자 하는 서식(일정한 규칙)을 적용을 하면 숫자 표시 방법이 간결해져서 이로인해 가독성이 좋아져서 보거나 읽기에 편해집니다.

여러가지 ( 휴대폰번호,전화번호,우편번호, 금액표시, 숫자의 단위구분,날짜형식,시간형식,백분율,,.....)들을 원하는 서식의 포맷으로 변경 처리

기본구문

기본 형식의 text 함수는 아래와 같고

=TEXT(서식을 지정할 값, "적용할 서식 코드")

"적용할 서식 코드"는 상황에 따라 종류가 많으므로, 여기서는 휴대폰번호와 일반전화번호에 -(하이픈)을 넣서 전화번호 서식에 맞게 처리하는것만 설명합니다.

 

휴대폰 번호 같은경우, 이번호가 숫자형식(맨 앞에 0숫자 없고 우측 정렬)과 문자형식(맨 앞에 0숫자 있고  좌측정렬)으로 표기가 되어서,

 

숫자,문자별 정렬 방식

 

 

아래에 각 개별적으로 사용예를 표시했다.

예1. text 함수 사용- 숫자 형식 휴대폰 번호 정리

 

숫자형식 휴대폰번호 text()함수로 서식적용

 

A2,A3 셀의 휴대폰는 앞에 0이 없어진 숫자형태로 입력이 된 상태이고, 1개는 중간번혹가 3자리인 옛날 번호 , 밑에꺼는 중간번호 4자리인 요즘 번호이다.

A2셀 휴대폰번호(9자리)를 text()함수에 적용시

"=TEXT(A2,"[>999999999]0##-####-####;0##-###-####")"

• A2는 참조할 값 위치 

• [>999999999] : 숫자의 길이를 체크하기위 9(숫자중 max값인 9 의미)를 9개 입력을 해주었다, 등호 기호가 > (작다)로 해서 

• 111234567 (9자리)여셔, 길이판단[>999999999] 9자리보다 크지 않으므로 바로 뒤에 있는 서식 "0##-###-####" 앞에 0을(0##) 붙여주고, 중간번호 3자리 형식(-###), 마지막 4자리 형식(-####)의 서식코드가 적용이 되어서 결과는 "011-123-4567" 로 표시가 된다.

 

A3셀 휴대폰번호(10자리)를 text()함수에 적용시

"=TEXT(A3,"[>999999999]0##-####-####;0##-###-####")"

• A3는 참조할 값 위치 

• [>999999999] : 숫자의 길이를 체크하기위 9(숫자중 max값인 9 의미)를 9개 입력을 해주었다, 등호 기호가 > (작다)로 해서 

• 1012345678 (10자리)여셔, 길이판단[>999999999] 9자리보다 크므로, 마지막에 있는 서식 "0##-####-####" 앞에 0을(0##) 붙여주고, 중간번호 4자리 형식(-####), 마지막 4자리 형식(-####)의 서식코드가 적용이 되어서 결과는 "010-1234-5678" 로 표시가 된다.

 

예2. text 함수 사용- 문자 형식 휴대폰 번호 정리

 

문자형식 휴대폰번호 text함수 서식

A2,A3 셀의 휴대폰는 앞에 0을 퐇함한 문자형태로 입력이 된 상태이고, 1개는 중간번혹가 3자리인 옛날 번호 , 밑에꺼는 중간번호 4자리인 요즘 번호이다.

A2셀 휴대폰번호(10자리)를 text()함수에 적용시,  수식은 위의 숫자 번호 형식에 사용한 수식과 동일하다, 문자번호라 맨앞에 0은 길이에서 체크가 안되므로 주의

"=TEXT(A2,"[>999999999]0##-####-####;0##-###-####")"

• A2는 참조할 값 위치 

• [>999999999] : 숫자의 길이를 체크하기위 9(숫자중 max값인 9 의미)를 9개 입력을 해주었다, 등호 기호가 > (작다)로 해서 

• "0111234567"은 눈에 보이는 자리수는 10자리이나, 엑셀의 text 함수의 길이 판단에서 맨 앞에 0은 체크가 안되는 것 같다, 그래서 길이 판단은 앞 0 제외한 9자리 체크인 9를 9개 입력 "[>999999999]"하여,  번호가 9자리보다 크지 않으므로 바로 뒤에 있는 서식 "0##-###-####" 앞에 0을(0##) 붙여주고, 중간번호 3자리 형식(-###), 마지막 4자리 형식(-####)의 서식코드가 적용이 되어서 결과는 "011-333-4444" 로 표시가 된다.

 

A3셀 휴대폰번호(11자리)를 text()함수에 적용시

"=TEXT(A3,"[>999999999]0##-####-####;0##-###-####")"

• A3는 참조할 값 위치 

• [>999999999] : 숫자의 길이를 체크하기위 9(숫자중 max값인 9 의미)를 9개 입력을 해주었다, 등호 기호가 > (작다)로 해서

• "01012345678" 눈에 보이는 자리수는  11개이나, text함수길이판단에서 맨앞에 0은 무시하는것 같아서, 길이판단시 맨앞 0을 제외한 자릿수가 "[>999999999]" 9자리보다 크므로, 마지막에 있는 서식 "0##-####-####" 앞에 0을(0##) 붙여주고, 중간번호 4자리 형식(-####), 마지막 4자리 형식(-####)의 서식코드가 적용이 되어서 결과는 "010-2222-5555" 로 표시가 된다.

 

예3.일반전화번호에 text함수 서식 적용 예이다.

문자형식 일반전화번호 text함수적용

숫자형식 일반전화번호 text함수적용

일반전화번호도 text함수의 수식중 길이판단 부분만 맞추어 주면 된다.

 

위 text함수  중 길이판단 부분 쓰는 방식을 잘 익혀보면 나중에 헷갈리지 않고 쉽게 사용이 가능하다.

 

Text 함수로 휴대폰번호에 하이픈(-) 이 없을 경우 간단한 처리방법을 나타냈고, 그외에 가독성이 떨어지는 데이터들을 text함수 사용해서 표준 서식에 맞게 변환이 가능하다.