[EXCEL] 엑셀 셀서식으로 텍스트 반복, 주민등록번호, 전화번호 형식 나타내기

엑셀은 단순히 데이터를 계산하고 정리하고 가공하는 쓰임새 외에 문서작업 용도로도 아주 유용하다. 특히 대량의 데이터가 들어가는 문서를 작업할 때에는 한글이나 워드보다 엑셀이 훨씬 편하다. 게다가 엑셀은 모든 보이는 문자나 양식을 일일이 타이핑할 필요 없이 간단한 지정만 해놓으면 데이터만 넣어도 원하는대로 표시되게 할 수 있다.

 

예를들어 엑셀은 숫자 1만 입력하면 "1만원"으로 표시되게끔 할 수 있고, 01000000000이라는 숫자만 입력해도 010-0000-0000의 형태로 표시되게끔 할 수 있다. 일일이 타이핑할 필요 없이 데이터 입력만으로도 원하는대로 출력이 되는 것이다.

 

이번 포스팅에서는 엑셀에서 데이터를 입력했을 때 같은 텍스트를 반복되게끔 하거나 주민등록번호의 형식, 전화번호의 형식으로 출력되게끔 하는 방법을 알아보고자 한다.

 

표시형식에 대해 잘 모르겠으면 내가 작성한 아래의 포스팅을 한번 보고 오는 것을 추천한다. 예시를 들어가며 풀어서 설명했으니 아주 쉽게 이해할 수 있을 것이다.

2024.05.15 - [슬기로운 생활/엑셀] - [EXCEL] 엑셀 셀서식 사용자정의 사용방법 (원하는대로 설정하기)

[EXCEL] 엑셀 셀서식 사용자정의 사용방법 (원하는대로 설정하기)

 

 

 

 

반복되는 텍스트 자동으로 넣기

우선 위와 같은 양식에 데이터를 넣으려고 하는데, 양식은 보고 받는 사람이 선호하는 방식대로 '00만원'의 형식으로 나타나게끔 하려고 한다. 일단 가장 간단한 방법은 데이터를 '00만원' 그대로 입력하는 것이다. 위는 직접 10만원이라는 데이터를 입력했다.

 

수량이 많지 않다면 이렇게 수기로 입력해도 상관은 없다. 굳이 함수나 표시형식을 건드는 것 보다는 그게 더 빠르다. 다만 위의 예만해도 8개 밖에 안되는데에도 불구하고 일일이 10만원, 20만원 입력하는데에 꽤나 시간이 걸렸다.

 

 

반응형

그리고 가장 큰 문제는 입력한 '10만원', '20만원' 들이 데이터형태 중 텍스트 형태로 인식이 되기 때문에 수식이 안걸린다. 바로 위 그림에서 실제로 sum 함수로 합계를 계산했는데 값이 0이 나온다. 이는 숫자가 없기 때문에 값이 0이 나온 것이다.

 

이를 해결하는 방법은 아주 간단하다. 셀서식에서 표시형식을 건드려서 숫자 값을 입력해도 자동으로 '00만원'의 형식으로 나오게끔 설정하면 된다.

 

 

우선 데이터가 들어갈 곳을 지정해준다. '00만원'의 형식으로 표시되어야 하는 곳이면 모두 지정해주면 된다. 그런 다음 셀서식창을 불러오는 단축키인 Ctrl + 1 을 눌러준다. 

 

 

셀서식창이 뜨면 섹서식창 중 표시형식에서 가장아래에 있는 사용자지정을 선택해준다. 그러면 보통은 기본으로 G/표준이라고 적혀있는데, 위 사진과 같이 #,###"만원" 이라고 적어준다.

 

이부분을 조금 더 상세하게 살펴보자면, #은 숫자를 나타내고, #,### 이라고 적은 것은 천단위마다 구분기호가 나오게끔 하는 것이다. 만약 표시형식을 #,###으로 설정하면, 1000을 입력하면 1,000이라고 표시된다. 그리고 뒤에 큰 따옴표""는 큰 따옴표 안에 있는 텍스트를 출력시켜주는 것이다.

 

이를 종합하면 숫자를 입력하면 천단위마다 소수점으로 구분되게끔 되고, 숫자 뒤에는 만원 이라는 텍스트가 같이 나오도록 설정한 것이다.

 

 

그리고 표시형식을 지정한 다음 숫자 10을 입력하니 위와 같이 10만원이라고 출력이 된다. 상단의 수식창을 보더라도 입력된 데이터는 10이라는 숫자를 그대로 유지하고 있고, 보이는 것만 '10만원'이라고 보이는 것이다. 같은 방법으로 년, 월, 일, 원, 개 등 원하는 텍스트가 반복되도록 지정할 수 있다. 

 

 

그리고 직접 입력이 아닌 표시형식만 변경했을 경우에는 데이터는 숫자 그대로 유지가 되고 있기 때문에 수식도 활용할 수 있다. 위 사진을 보면 sum 함수를 통해 계산된 것을 볼 수 있다.

 

 

전화번호 형식 적용하기

표시형식을 활용한 다음 예시는 전화번호 형식을 자동으로 적용하는 것이다. 보통 전화번호를 입력하라고 하면 - 기호를 통해서 위와 같이 구분해서 입력한다. 하지만 이 역시 매번 -를 쳐야되면 불편하다. 따라서 숫자만 입력하면 자동으로 자리가 구분되게끔 하려고 한다.

 

 

역시 입력할 곳을 지정한다음 ctrl + 1 을 누른다.

 

 

셀서식창이 뜨면 표시형식에서 가장 아래쪽에 있는 사용자지정을 선택한다. 그리고 형식란에 000"-"0000"-"0000 이라고 입력을 한다.

 

앞에서 숫자가 들어가는 자리는 #을 쓴다고 했다. 0도 마찬가지로 숫자가 들어가는 자리이다. 다만 #은 숫자가 비어있다면 공란으로 나오는데, 예를들어 ###으로 세 자리로 표시형식을 설정했을 때 숫자를 15로 입력하면 15로 출력된다.

 

이에반해 0은 숫자가 비어있다면 그 자리가 0으로 출력되는데, 예를들어 000으로 세 자리로 표시형식을 설정했을 때 숫자 15를 입력하면 015로 출력된다. 전화번호의 경우 앞자리가 010으로 시작하기 때문에 #으로 지정하면 가장 앞 0이 공란으로 나오게 된다. 따라서 0으로 표시형식을 설정한다.

 

그리고 01000000001이라는 숫자를 입력하면 위와 같이 자동으로 전화번호 형식으로 구분되어 나온다. 여전히 데이터 자체는 01000000001이 들어가고 표시형식만 변경된 것이다. (수식창 가장 앞에 0은 없는데, 가장 앞 0은 숫자로 치면 무의미하므로 자동으로 없어진다.)

 

 

이를 조금 더 응용하면, 아에 앞자리는 텍스트로 고정시켜버리고, 뒤에 8자리만 입력하도록 만들수도 있다. 그러면 010을 제외한 뒤 8자리만 입력하더라도 자동으로 010-****-****의 형식으로 표시되어 나오게 된다.

 

 

 

주민등록번호 형식으로 나타내기

주민등록번호 형식도 전화번호 형식과 같은 방식으로 설정하면 된다. 앞자리 6자리를 0으로 입력하고, 가운데 텍스트로써의 - 를 입력하고, 뒤에는 0으로 7자리를 채워준다. #으로 하게되면, 가장 앞자리가 0이 오는 주민등록번호의 경우 가장 앞자리 0이 생략되기 때문에 #이 아닌 0으로 표시형식을 지정한다.

 

 

마무리

이번 포스팅에서는 지난번 표시형식에 대한 기본적인 기호 및 설명에 이어 응용하는 방법에 대해 알아보았다. 표시형식은 어렵지 않게 여러가지로 응용할 수 있기 때문에 아주 유용하다.