엑셀 표시형식 - 텍스트, 숫자 변환 방법

엑셀에 대해서 알려드리는 부분이 점점 마이너한 지식을 알려드리게 되는 듯합니다.

 

특히 제가 엑셀 작업하면서 편하게 사용한 부분을 넣으려고 하다 보니 엑셀 포스팅이 점점 어려워지는 듯합니다. 

 

엑셀 표시형식에 대해서 말씀드리는 이유는 시스템에서 대량의 데이터를 다운로드하였을 때,

 

분명하게 숫자로 입력되어야 하는데, 텍스트로 인식되어 나오는 경우가 있습니다.

(반대의 경우도 있습니다.)

 

이런 경우 표시형식이 달라 VLOOKUP이나 SUMIF 등의 함수가 인식을 못하는 경우가 종종 있습니다.

 

가끔 한 번이라면 수정해주면 되지만, 지속적 작업이 필요한 경우 이를 자동으로 해결하기 위해 수식 연결이 필요합니다.

 

이를 알려드리고자 '표시형식'에 대해 포스팅하게 되었습니다.

 

표시형식 위치

 

홈 > 표시형식

 

서식의 기본이기에 가장 앞의 '홈' 탭에 있습니다.

 

간단하게 표시형식 변경을 원하시면 해당 영역에서 선택하시면 되겠습니다.

표시형식1

표시형식에 따른 값 표시

 

아래와 같이 동일한 값에도 표시형식에 따라서 표현되는 값이 다름을 알 수 있습니다.

 

일반의 경우 숫자와 텍스트를 자동으로 구분하고 있습니다. 

표시형식2

표시형식 상세 보기

 

표시형식을 조금 더 내가 원하는데로 변경하기 위해서는 셀서식으로 들어가셔야 합니다.

 

셀서식으로 들어가는 방법은 아래와 같이 2가지가 있습니다.

 

① 홈 > 표시형식 오른쪽 화살표 클릭

 

② 변경하고자 하는 셀 선택 > 마우스 우클릭 > 셀 서식(F) 선택

 

대부분의 범주는 눌러보시면 사용하는데 어려움이 없으실 듯합니다.

 

하지만, 마지막 범주인 '사용자 지정'의 경우 약간의 공부가 필요합니다.

 

다음에 '사용자 지정'의 몇 가지 사용법을 공유드리겠습니다.

(기능이 워낙 많아서 저도 전부는 모르고, 자주 사용하는 것 위주로 공유드리겠습니다.)

표시형식3

텍스트 → 숫자 변환

 

텍스트로 값이 입력된 경우 SUM 등의 수식에서 인식을 못하는 경우가 있습니다.

 

그리고 VLOOKUP 사용 시 지정한 값과 비교 대상의 표시형식이 달라 일치하는 값을 못 찾는 경우도 있습니다.

 

1, 2건의 자료인 경우 하나씩 수정하면 되지만, 수백, 수천 건의 데이터는 손으로 변경이 불가능하기에 쉽게 변환하는 방법을 알려드리고자 합니다.

 

① 오류표시 클릭하여 숫자로 변환

 

표시형식에 상관없이 숫자가 텍스트 형식으로 저장되면 시스템에서 오류표시가 나타납니다.

 

왼쪽 위에 녹색(사용자마다 색깔 변경 가능)이 있는 셀을 선택하면 느낌표가 나타나는데,

 

느낌표 클릭 시 '숫자로 변환(C)' 선택이 가능합니다.

 

'숫자로 변환' 클릭 시 값의 표시형식이 '일반'으로 함께 변경되면서 값이 숫자 바뀌게 됩니다.

표시형식4

② 1을 곱하여 숫자로 변환

 

신기한 게 표시형식이 텍스트여도 엑셀에서 숫자로 인식하는 값은 연산이 가능합니다.

 

이에 해당 값에 변경 사항 없게 1을 곱하여 숫자로 변경이 가능합니다.

 

이 내용이 중요한 이유는 VLOOKUP 사용 시에 숫자 변환 과정 없이 바로 적용이 가능하기 때문입니다.

 

VLOOKUP 수식 예) =VLOOKUP(F3*1,H:I,2,0)

표시형식5

숫자 → 텍스트 변환

 

숫자를 텍스트로 변환하는 작업의 경우 자주 사용할 일은 없을 듯 합니다.

 

그런데, 정말 한 번씩 필요할 때가 있고 표시형식 변경만으로는 텍스트화가 되지 않아 방법을 공유드립니다.

 

숫자를 텍스트로 변환하는 방법은 의외로 3가지나 있습니다.

 

① 표시형식 변경

 

표시형식을 변경하더라도 오른쪽 'SUM 적용 시'에서 확인할 수 있듯이 숫자로 인식이 됩니다.

 

표시형식 변경 후 > 신규로 직접 값 입력 or 클릭한 후 Enter 키(직접 값 입력과 동일)입니다.

 

그렇게 하면 가장 아래와 같이 숫자가 텍스트로 저장되었다는 오류 표시가 생기고 'SUM 적용 시'로 알 수 있듯이 숫자로 인식이 안되게 됩니다.

표시형식6

② 숫자 앞에 ' (어포스트로피) 입력

 

가장 흔하게 알고 계신 방법이라 생각됩니다.

 

셀 가장 앞에 ' (어포스트로피)를 찍으면 뒤에 모든 값은 텍스트로 입력이 됩니다.

 

문서 작성하면서 숫자나 함수를 텍스트로 표현하고 싶을 때 가장 많이 사용합니다.

표시형식7

③ TEXT 함수 사용

 

TEXT 함수 사용법은 모르시는 분들이 많을 듯합니다.

 

TEXT 함수는 원하는 값을 강제적으로 원하는 표시형식의 텍스트로 변경을 해주는 함수입니다.

 

아래와 같이 셀을 연결하고 "@"를 덧붙여 주면 추가 작업 없이 텍스트로 변경이 됩니다.

( @ 뒤에 문자를 넣으면 그대로 출력 됨)

 

정수일 경우는 "#" 을 입력하셔도 동일한 결과를 얻을 수 있습니다.

(소숫점일 경우 표시형식 차이로 다르게 보임)

 

이 기능은 자재코드와 같은 고윳값이 숫자로 인식되어 있을 경우 텍스트로 변환해주는 작업입니다.

 

이 기능도 VLOOKUP 적용 시 표시형식 차이로 에러가 발생하는 부분을 보완하기 위해 찾은 방법입니다.

 

TEXT 함수는 표시형식의 '사용자 지정'과 동일하므로 '사용자 지정'을 추가로 공부하시면 도움이 되겠습니다.

표시형식8

예) VLOOKUP 함수에 적용 예시
    =VLOOKUP(TEXT(J3,"@"),A:B,2,0)

 

가고자 하는 길에 도달하는 방법은 여러가지가 있습니다.

엑셀에도 동일하게 적용되는 말 일 듯합니다.

 

본인이 원하는 결과 값에 대해서 공통점을 찾는 다면 더 쉽고 빠르게 자신만의 함수를 사용하여 작업 가능하리라 생각됩니다.

 

나에게 맞는 함수를 찾으셨으면 합니다.

모두들 적게 일하고 많이 버세요~