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

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

엑셀에서 숫자,날짜를 원하는 형태로 표시하려면, 예를 들면 숫자 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/표준") => 參阡伍百

 

 

[ 엑셀 텍스트 함수 ]

“엑셀함수 TEXT - 숫자,날짜를 원하는 형태의 텍스트로 바꾸기”의 5개의 댓글

  1. 안녕하세요

    섹션1 #,##0 값이 양수일때는 파란색으로, 천단위 콤마를 표시
    이렇게 설명을 하셨는데요

    적용해보면 색이 변하지 않습니다.
    제가 잘못 사용하는 건가요? 아니면 설명을 잘못하신 걸까요?

    B8 셀에 -8000을 입력하고 수식을 =TEXT(B8,"#,##0;△#,##0;0.00;@고객님") 이렇게 넣으면
    검은색으로 △8000 이라고 나옵니다.

    1. 안녕하세요. 위에 보시면 "단, 색상표시 등 일부는 셀서식에서는 적용되지만 TEXT함수에서는 적용안됨"이라고 되어 있는데 잘 안보이네요.
      빨간색으로 "색상표시, 출력된 숫자의 좌/우 정렬, 문자반복 등 셀에서만 표현가능한 기능은 TEXT함수에서는 적용되지 않음"이라고 잘 보이게 해 두었습니다.
      TEXT함수는 함수이름 그대로 서식을 텍스트로 표현하기 때문에 색상, 정렬등의 기능을 제공하지 않고 색상기능을 적용해도 검정색으로만 표시됩니다.
      감사합니다.

      1. 설명이 있었는데 제대로 못 보고 열심히 아래쪽 수식만 보고 따라 해봤었네요.
        답변 감사합니다.

        엑셀에서 다른 셀의 값을 참조해서 콤마와 색을 넣는 방법은 없을까요?

        100,000 -10,000
        A1셀 B1셀

        100,000원 - 10,000원 = 90,000원
        A1 B1 A1-B1
        빨간색 파란색

        아니면

        100,000원 + (-10,000)원 = 90,000원
        빨간색 파란색

        이렇게 나타나게 하는 방법은 없을까요?

          1. 셀을 하나로 합쳐서 사용하니까 셀 서식으로는 처리가 안 됐습니다.
            셀 값의 일부는 빨간색 일부는 파란색으로 표시를 해야 해서요.

            검색해서 VBA로 해결했습니다.
            감사합니다.

답글 남기기

이메일 주소를 발행하지 않을 것입니다.