엑셀함수 TEXT – 숫자,날짜를 원하는 형태의 텍스트로 바꾸기

공개됨 글쓴이 Admin댓글 남기기

엑셀에서 숫자,날짜를 원하는 형태로 표시하려면, 예를 들면 숫자 50000을 50,000으로 표시하려면, 셀서식을 지정하거나 TEXT함수를 사용하는 방법이 있다. 오늘은 TEXT함수로 숫자, 날짜를 원하는 형태의 텍스트로 바꾸어서 표시하는 방법을 알아보자.

구문(Syntax)
TEXT(value,format_text)

숫자(날짜포함, 엑셀에서 날짜는 숫자로 저장된다)를 원하는 형태의 텍스트로 바꾸어서 표시해준다.

인수 :
value : 텍스트로 바꿀 값(서식코드를 적용할 값)
format_text : 적용할 텍스트 형식(서식코드)

 

1. 기본 사용법

아래의 간단한 예를 보고 TEXT함수를 이해해 보자.

동일한 값이라도 서식코드에 따라 표시형식이 달라진다. 사실 이 서식코드는 셀서식을 지정할 때 사용하는 서식코드와 기능이 동일하다(단, 색상표시 등 일부는 셀서식에서는 적용되지만 TEXT함수에서는 적용안됨)

엑셀 TEXT함수 기본사용법

=TEXT(B5,”#,##0″) => 10,000
=TEXT(B6,”000,000″) => 010,000
=TEXT(B6,”yyyy-mm-dd”) => 2019-06-12
=TEXT(B7,”hh:mm AM/PM”) => 09:05 AM
=TEXT(B8,”# ?/?”) => 1/2
=TEXT(B9,”[<=9999999]###-####;(0##) ###-####”) => (02) 250-3456

 

[서식코드를 셀서식 대화상자에서 복사하기]

위에서 본대로 서식코드가 복잡하므로 서식코드를 직접 만드는 것보다는 엑셀에 내장된 기존의 서식코드를 복사하여 필요에 맞도록 수정해서 쓰는 것이 효율적이다.

다음 순서로 기존의 서식코드를 찾아 보자.

1)  리본메뉴 > “홈” 탭 > “표시 형식” 아래에 있는 곳을 클릭하거니 Ctrl + 1를 누른다.

엑셀 셀서식 대화상자 열기

 

2) 셀 서식 대화상자에는 다양한 선택 옵션이 준비되어 있다. 먼저 범주에서 원하는 유형을 선택하고 오른쪽에 나타나는 옵션에서 표시형식을 선택한다.

엑셀 셀서식 지정

 

3) 범주에서 “사용자 지정”을 선택하면 오른쪽에 “#,##0” 와 같은 문자들이 보인다. 이 문자를 “서식코드”라고 부르는데 Ctrl+C를 눌러 복사한다.

엑셀 셀서식코드 복사

 


2. 표시 형식별 상세 사용법

위에서는 간단한 예만 들었는데 표시형식별로 상세한 사용법을 더 알아보자.

 

1) 숫자 표시 형식

다음과 같이 #과 콤마(,)를 이용해서 숫자 500000의 포맷을 바꾸어 보자.

=TEXT(500000,”#,###”) => 500,000로 표시해 주는데

두번째 인수로 사용된 서식코드에서

  • #은 숫자를 표시하라는 것이고
  • 콤마(,)는 천단위마다 콤마를 표시하라는 뜻이다.

 

위의 예 말고도 아래와 같이 다양한 형태로 포맷을 바꿀 수 있다.

엑셀 TEXT 함수

=TEXT(B14,”#,###”) => 500,000
=TEXT(B15,”#,###”) => 표시안됨
=TEXT(B16,”#,##0″) => 0
=TEXT(B17,”00000,000″) => 00,500,000
=TEXT(B18,”#,###,”) => 5,000
=TEXT(B19,”#,###,,”) => 5
=TEXT(B20,”#,##0 ;△#,##0″) => 10,000
=TEXT(B21,”#,##0 ;△#,##0″) => △10,000

 

[서식코드에 사용되는 문자]

서식코드에 사용되는 문자는 #,0,콤마(,) 이외에도 다음과 같이 몇가지가 더 있다. 이 문자를 조합하면 아주 다양한 형태로 숫자를 표시할 수 있다.

문자 의미
# 숫자하나 또는 여러 개를 의미, 유효자릿수만 표시(#으로 표시한 서식의 길이보다 입력한 값이 짧은 경우에는 값의 길이 만큼 표시됨)
0 0으로 표시한 서식의 길이보다 입력한 값이 짧은 경우 나머지는 0으로 채움
? 숫자를 표시, 소수점 고정된 위치에 표시할 때 사용할 수 있다. ?로 표시한 서식의 길이보다 입력한 값이 짧으면 나머지는 공백으로 채움
, 천단위 표시용 쉼표(콤마)
_(밑줄) 숫자에서 공백을 표시할 때 사용, 밑줄 다음의 문자와 같은 너비의 공백을 만들 때 사용(예: “_)” 을 입력하면 “)”의 문자크기만큼 공백이 생김)
@ 문자를 그대로 출력, @을 2번 반복하면 입력된 문자를 2번 반복해서 출력
* * 뒤의 문자를 셀 너비에 맞게 반복

위의 문자를 이용해서 서식코드를 적용한 예를 좀 더 살펴보자.엑셀 서식코드 적용결과

=TEXT(D25,”#,##0″) => 10,000
=TEXT(D26,”000,000_ “) => 000,010
=TEXT(D27,”???,??0.0??”) => 110.0
=TEXT(D28,”???,??0.0??”) => 10.532
=TEXT(D29,”#,##0″) => 10,000
=TEXT(D30,”###,##0_)”) => 5,000
=TEXT(D31,”@고객님”) => 홍길동고객님
=TEXT(D32,”*+#”) => 500 (문자반복은 TEXT함수에서는 적용되지 않음)

 

[서식코드 구조]

위의 예에서 사용된 수식=TEXT(-10000,”#,##0 ;△#,##0″) 를 보면 서식코드가 좀 더 길고 형식이 좀 복잡한데 2개의 섹션으로 구성된 서식코드이다. 그래서 양수일때는 천단위 콤마를 표시하고, 음수일때는 천단위 콤마를 표시하되 숫자앞에 삼각형(음수표현)을 붙여서 표시해 준 것이다.

서식코드는 원래 다음 그림과 같이 4개의 섹션으로 구성되는데 필요에 따라서 한두개의 섹션만 사용할 수도 있다.

엑셀 서식코드 구조

4개의 섹션을 전부 적용하면 4가지의 경우(입력된 값이 양수일때,음수일때,0일때,문자일때)가 한번에 적용되어 마치 조건부 서식을 이용하는 것처럼 값의 상태에 따라 모양을 다르게 표시할 수 있다.

위의 서식코드 “#,##0;△#,##0;0.00;@고객님” 을 적용하면 다음과 같이 각각의 입력값에 따라 4가지 경우가 표시된다.

엑셀 서식코드 적용

 

위 서식코드를 풀이해 보면 각 섹션의 의미는 다음과 같다.

섹션 서식코드 의미
섹션1 #,##0 값이 양수일때는 파란색으로, 천단위 콤마를 표시
섹션2 △#,##0 값이 음수일때는 빨간색으로, 음수기호와 함께 천단위 콤마를 표시
섹션3 0.00 값이 0일때는 0.00으로 표시
섹션4 @고객님 값이 문자일 때는 입력된 값에 “고객님”이라는 값을 붙여서 표시

서식코드를 네개 다 쓸 필요는 없고 필요에 따라 한두개만 써도 되는데 다음과 같이 적용된다.

  • 한 개의 코드 섹션만 지정할 경우 이 섹션은 모든 숫자에 적용된다.
  • 두 개의 코드 섹션만 지정할 경우 첫 번째 섹션은 양수 및 0에 적용되고 두 번째 섹션은 음수에 적용된다.
  • 특정 코드 섹션을 건너뛰고 그 다음 코드 섹션을 포함하려면 건너뛸 부분에 세미콜론(;)을 사용한다.

 

2) 날짜,시간 표시형식

날짜 및 시간을 표시할 때 사용하는 서식코드는 다음과 같다. 일반적으로 다른 코드값과 조합하여 사용한다.

  • y,m,d : 년,월,일을 표시
  • a : 요일을 표시
  • h : 시간을 표시
  • m : 분(h(시간) 또는 s(초)와 함께 사용해야 분으로 표시, 단독으로 사용시 월로 표시됨)
  • s  : 초를 표시
  • AM/PM, A/P : 오전/오후를 표시

<날짜,시간 서식코드 적용예>

엑셀함수 TEXT - 날짜서식코드

 =TEXT(B37,”yyyy”) => 2019
=TEXT(B38,”yy”) => 19
=TEXT(B39,”mm”) => 06
=TEXT(B40,”m”) => 6
=TEXT(B41,”mmm”) => Jun
=TEXT(B42,”mmmm”) => June
=TEXT(B43,”mmmmm”) => J
=TEXT(B44,”dd”) => 05
=TEXT(B45,”d”) => 5
=TEXT(B46,”aaa”) => 수
=TEXT(B47,”aaaa”) => 수요일
=TEXT(B48,”hh”) => 09
=TEXT(B49,”h”) => 9
=TEXT(B50,”mm:ss”) => 05:07
=TEXT(B51,”m:ss”) => 5:07
=TEXT(B52,”ss”) => 07
=TEXT(B53,”s”) => 7
=TEXT(B54,”hh:mm AM/PM”) => 09:05 AM
=TEXT(B55,”hh:mm A/P”) => 09:05 A
=TEXT(B56,”[$-ko-KR]AM/PM hh:mm”) => 오전 09:05

 

3) 분수 표시형식

분수를 표시할 때 사용하는 서식코드는 다음과 같다.

분수로 표시할 때 입력값이 정확히 분수로 맞아 떨어지지 않으면 가장 가까운 자리의 분수 값으로 반올림되는 점을 주의해야 한다. 예를 들어 입력값 0.33을 한 자릿수 분모로 표현하면 1/3이 된다.

<분수표시 서식코드 적용예>

엑셀 TEXT함수 - 분수 표시

=TEXT(B61,”# ?/?”) => 1/2
=TEXT(B62,”# ?/?”) => 4 1/5
=TEXT(B63,”# ??/??”) => 3/10
=TEXT(B64,”# ???/???”) => 3 16/113
=TEXT(B65,”# ?/2″) => 1/2
=TEXT(B66,”# ?/4″) => 2/4
=TEXT(B67,”# ?/8″) => 4/8
=TEXT(B68,”# ?/16″) => 8/16
=TEXT(B69,”# ?/10″) => 5/10
=TEXT(B70,”# ?/100″) => 50/100

 

4) 기타 표시형식

숫자만 입력하면 주민등록번호 형태로 표시하거나 숫자를 한글이나 한자로 표시할 수 있는 다음과 같은 서식코드가 있다.

<기타 서식코드 적용예>

엑셀 TEXT함수 - 기타표시형식

=TEXT(B75,”000000-0000000″) => 990501-1231346
=TEXT(B76,”[DBNum4][$-ko-KR]G/표준”) => 삼천오백
=TEXT(B77,”[DBNum1][$-ko-KR]G/표준”) => 三千五百
=TEXT(B78,”[DBNum2][$-ko-KR]G/표준”) => 參阡伍百

 

 

[ 엑셀 텍스트 함수 ]

댓글 남기기

이메일은 공개되지 않습니다.