엑셀

엑셀 TEXT 함수로 서식 지정해서 텍스트로 변경하기

콘파냐 2017. 5. 22. 18:18
반응형

엑셀 TEXT 함수에 대해 설명하기 전에 한가지 짚고 넘어갈 것이 있다.


셀에 서식을 지정하면 셀 내용이 지정된 서식에 맞게 바뀐다고 착각하기 쉽다. 그러나 서식을 적용하는 것과 원래 값이 바뀌는 것은 별개의 문제다.


예를들어 엑셀에서 날짜를 표현하는 서식은 다양하지만 날짜 정보를 보관하는 방법은 한가지다.


엑셀은 날짜 정보를 1900년 1월 1일을 기준으로 경과한 날짜수 만큼의 수로 저장한다.


날짜 서식이 적용되어있는 셀을 일반 서식으로 바꿔보면 위 그림과 같은 형태의 실제 값(35836)으로 표시되는 것을 확인할 수 있다.


이에 반해 TEXT 함수를 사용하면 서식 적용과 동시에 서식이 적용된 형태의 텍스트로 아에 값을 변경시켜 준다.


  • 엑셀 TEXT 함수

형식

TEXT(값, 서식 코드)


날짜 서식이 적용된 셀에 대해서 연도를 뽑아보자.


실수하기 쉬운 예)

날짜 1900-01-10 에 대해서 년도만을 뽑아내기 위해 LEFT 함수로 텍스트의 왼쪽의 4개의 문자를 뽑아내기로 하였다.

하지만 앞에서 말 했듯이 날짜의 실제 값과 셀에 표시되는 값은 다르므로 예상치 못한 결과가 나온다.


제대로 된 방법)

서식코드에 대한 내용을 반드시 이해해야 한다. (다음 글 참고)

사용자지정서식 숫자, 텍스트, 색, 날짜 지정

날짜 서식 이해와 만들기


TEXT(B2, "yyyy")

B2셀의 실제 값에 서식코드 "yyyy"를 적용해서 텍스트로 반환한다.

이렇게 TEXT 함수의 두 번째 인수는 사용자지정 서식에 사용하는 서식코드를 사용한다.


결론은 사용자 지정서식에 서식코드를 사용하면 실제 값은 그대로임.

TEXT 함수로 서식코드를 적용하면 서식이 적용된 형태의 텍스트로 반환함.


  • 엑셀 TEXT 함수 활용 예

특정 값 추출

TEXT 함수로 뽑아낸 텍스트에 --를 붙여서 숫자 형태로 바꿀 수 있다.

--TEXT(B2, "yyyy")

--의 의미는 -1을 두번 곱한다는 것이다. 텍스트가 숫자계산에 사용되면 자동으로 숫자로 변경되어 계산되는 원리를 이용한 것이다.

앞의 예에서 --를 붙이면 숫자 1900이 된다.

이렇게 TEXT 함수로 서식을 지정하면 적용된 서식에 따라 원래의 값이 변경될 수 있다는 것을 명심하자.


특정 서식에 맞게 형식을 변경하여 데이터 값 변경①

날짜입력이 19990210 형태로 되어 있는 표가 있다면 이 셀에 날짜 서식을 바로 적용하면 엉뚱한 값이 나올 수 밖에 없다. 이런 경우 TEXT 함수를 사용하여 해결한다.

TEXT(B2, "0000-00-00")

19990210이라는 숫자에 위와 같이 서식을 적용하여 텍스트형태로 변경한다. 


이 다음이 중요한데 한번 이 식에 --를 붙여보자.

--TEXT(B2, "0000-00-00")

서식에 맞게 데이터의 값이 변경되었음을 알 수 있다.


이제 직접 날짜서식을 선택 적용하면 된다.



특정 서식에 맞게 형식을 변경하여 데이터 값 변경②

앞의 예는 값이 숫자로 이루어진 경우라서 쉽게 숫자서식 코드를 적용할 수 있었다. 만약에 1999.02.10 와 같이 숫자가 아닌 값이라면 어떻게 할까?

이 상태로는 숫자 서식코드가 적용되지 않으므로 .을 날짜서식에 맞는 꼴로 변경해서 엑셀에서 알아먹을 수 있게 해줘야한다.

-나 /형태로 바꾸는 게 좋을 듯 싶다. 이 때 SUBSTITUTE 함수를 사용하면 된다.


이제 위 식에 --를 붙여주면 날짜 데이터로 변경된다.

=--SUBSTITUTE(B2, ".", "/")


위 내용들을 응용해서 서식과 맞지 않게 쓰여진 셀의 값을 편리하게 변경할 수 있다.

반응형