엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지

합계를 냈는데 0이 나옵니다. VLOOKUP은 #N/A를 뱉고, COUNTIF는 분명히 있는 값을 못 찾습니다. 화면에는 아무 문제 없어 보이는 숫자인데도 그렇습니다.

대부분 숫자가 텍스트로 저장된 경우입니다. 다른 시스템에서 내려받은 파일, 웹페이지에서 복사한 표, ERP에서 뽑은 자료에서 거의 항상 발생합니다.

1단계. 텍스트인지 확인하기

세 가지 방법이 있고, 위로 갈수록 확실합니다.

가장 확실한 방법

=ISNUMBER(A2)

FALSE가 나오면 텍스트입니다. 셀 하나만 확인하지 말고 아래로 채워서 전체를 보세요. 일부 행만 텍스트인 경우가 가장 찾기 어렵습니다.

눈으로 확인하는 방법

엑셀은 숫자를 오른쪽, 텍스트를 왼쪽으로 정렬합니다. 정렬을 따로 지정하지 않았는데 값이 셀 왼쪽에 붙어 있다면 텍스트입니다.

상태표시줄로 확인하는 방법

숫자 열을 드래그 선택했을 때 화면 아래 상태표시줄에 합계가 표시되지 않고 개수만 나온다면 텍스트입니다.

2단계. 숫자로 바꾸기

상황에 따라 네 가지 방법이 있습니다.

방법 1. 곱하기 1 (가장 확실)

빈 셀에 1 입력 후 복사 (Ctrl + C)
문제가 되는 열 전체 선택
우클릭 → 선택하여 붙여넣기
연산에서 곱하기 선택 후 확인

모든 값에 1을 곱하면서 강제로 숫자 연산을 시키는 방식입니다. 값은 그대로 두고 형태만 바꿉니다. 열이 여러 개여도 한 번에 처리됩니다.

방법 2. 오류 검사 표시 이용

셀 왼쪽 위에 초록색 삼각형이 있다면, 열 전체를 선택하고 나타나는 느낌표 아이콘 → 숫자로 변환을 누르면 됩니다.

가장 간단하지만 초록 삼각형이 항상 나타나는 건 아닙니다. 안 보인다면 다른 방법을 쓰세요.

방법 3. 텍스트 나누기 (한 열씩, 매우 빠름)

열 하나 선택
데이터 → 텍스트 나누기
아무것도 바꾸지 말고 마침 클릭

나누는 게 목적이 아니라, 엑셀이 데이터를 다시 해석하게 만드는 게 목적입니다. 한 번에 한 열만 되지만 클릭 세 번으로 끝납니다.

방법 4. VALUE 함수 (원본을 남겨야 할 때)

=VALUE(A2)

원본을 보존해야 하거나 매번 새 파일이 들어와서 자동화가 필요할 때 씁니다.

3단계. 반대 경우 — 숫자를 텍스트로 지켜야 할 때

사업자등록번호, 우편번호, 사번처럼 앞자리가 0으로 시작하는 값은 숫자로 바뀌면 0이 사라집니다. 0100이 100이 되는 식입니다.

이런 열은 입력 전에 셀 서식을 텍스트로 지정하거나, 값 앞에 작은따옴표(')를 붙여 입력하세요. 이미 0이 날아간 뒤에는 복구할 수 없습니다.

자릿수만 맞추면 되는 경우라면 표시 형식으로 처리할 수도 있습니다. Ctrl + 1 → 사용자 지정 → 0000을 입력하면 값은 숫자로 두고 화면에만 0100으로 보입니다.

자주 막히는 지점 세 가지

① 곱하기 1을 했는데도 안 바뀜

값 안에 천 단위 쉼표나 통화 기호가 문자로 들어 있는 경우입니다. 1,234원 같은 형태는 곱하기로 처리되지 않습니다.

먼저 문자를 제거해야 합니다.

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"원",""))

② TRIM을 써도 공백이 안 없어짐

웹페이지에서 복사한 데이터에는 일반 공백이 아니라 줄바꿈 없는 공백(문자 코드 160)이 들어 있는 경우가 많습니다. TRIM은 이걸 못 지웁니다.

=VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), " ")))

웹에서 가져온 표가 유독 안 고쳐진다면 이 경우일 가능성이 높습니다.

③ 마이너스 부호가 숫자 뒤에 있음

일부 회계 시스템은 1234-처럼 부호를 뒤에 붙여서 내보냅니다. 엑셀은 이걸 숫자로 인식하지 못합니다.

=IF(RIGHT(A2,1)="-", -VALUE(LEFT(A2,LEN(A2)-1)), VALUE(A2))

자주 묻는 질문

Q. 셀 서식을 '숫자'로 바꿨는데 왜 안 되나요?

셀 서식은 보여주는 방식만 정할 뿐, 이미 저장된 데이터의 형태는 바꾸지 않습니다. 텍스트로 저장된 값은 서식이 숫자여도 여전히 텍스트입니다. 위의 네 가지 방법 중 하나로 실제 값을 바꿔야 합니다.

Q. 매달 같은 파일을 받는데 매번 고쳐야 하나요?

파워쿼리로 가져오면 데이터 형식을 한 번만 지정해두고 새로고침으로 반복할 수 있습니다. 데이터 → 데이터 가져오기로 불러온 뒤 편집기에서 열 형식을 10진수로 지정해두세요.

Q. 어떤 값이 텍스트인지 한 번에 찾으려면?

조건부 서식에 =AND($A2<>"", NOT(ISNUMBER($A2))) 규칙을 넣으면 텍스트인 셀만 색으로 표시됩니다. 큰 파일에서 흩어져 있는 텍스트 값을 찾을 때 유용합니다.

Q. 구글 시트에서도 같은가요?

ISNUMBER, VALUE, SUBSTITUTE 모두 동일하게 작동합니다. 다만 구글 시트는 데이터 → 텍스트를 열로 분할이 자동으로 형식을 재해석하는 경우가 많아 더 간단히 해결되기도 합니다.

정리

확인은 =ISNUMBER() — 왼쪽 정렬도 신호
변환은 곱하기 1이 가장 확실
셀 서식 변경은 해결책이 아님
안 고쳐지면 쉼표·CHAR(160)·뒤에 붙은 마이너스를 의심

관련 글

거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
두 파일 대사, 눈으로 비교하지 말고 COUNTIF로 끝내기
엑셀 중복 데이터, 지우기 전에 먼저 확인하는 법
엑셀 근무시간 계산, 합계가 이상하게 나오는 진짜 이유