이 글의 목적은 엑셀에서 숫자처럼 보이지만 계산되지 않는 텍스트 값을 정확한 숫자 형식으로 변환하는 검증된 방법을 상황별로 제시하여, 실무에서 오류 없이 합계·평균·피벗 분석·파워쿼리 모델링을 바로 수행할 수 있도록 돕는 것이다.
왜 “텍스트 숫자”가 생기는가
보고서·회계자료·설비 데이터 로거 등에서 복사·붙여넣기 또는 CSV 가져오기 과정에서 숫자가 텍스트로 들어오는 경우가 많다. 대표적인 원인은 다음과 같다.
- 숫자 앞뒤 공백 또는 숨은 문자(예: 불연속 공백
CHAR(160))가 포함되어 있는 경우이다. - 천 단위 구분 기호, 소수점 기호의 로캘 차이(예:
1.234,56vs1,234.56)가 있는 경우이다. - 숫자 앞에 작은따옴표(
')로 강제 텍스트가 입력된 경우이다. - 하이픈 포함 형식(예:
2024-01)이 날짜·텍스트로 혼재된 경우이다. - 수식 결과가 텍스트로 고정되어 복사된 경우이다.
주의 : 셀 서식만 ‘숫자’로 바꿔도 데이터 형식 자체가 텍스트라면 계산되지 않는다. 반드시 값 자체의 데이터 유형을 변환해야 한다.
빠르게 해결: 오류 검사의 느낌표 버튼
셀 좌측 상단의 초록색 삼각형이 보이면 엑셀이 “텍스트로 저장된 숫자”를 감지한 것이다. 셀을 선택하고 느낌표 버튼을 눌러 ‘숫자로 변환’을 선택한다.
- 장점: 가장 빠른 수동 처리 방법이다.
- 제한: 선택된 범위에만 적용되며 사용자 확인이 필요하다.
가장 확실한 기본기: VALUE와 NUMBERVALUE 함수
VALUE는 일반적인 영어권 구분 기호를 따르는 텍스트를 숫자로 바꾼다. 다국어·다른 구분 기호가 섞인 데이터는 NUMBERVALUE가 안전하다.
| 상황 | 권장 함수 | 예시 수식 | 설명 |
|---|---|---|---|
| 일반 텍스트 숫자 | VALUE | =VALUE(A2) | A2가 "1234.56" 같은 형식일 때 정확히 변환한다. |
| 콤마가 소수점인 유럽식 | NUMBERVALUE | =NUMBERVALUE(A2, ",", ".") | A2가 "1.234,56"일 때 소수점은 콤마, 천 단위는 점으로 지정한다. |
| 공백·숨은 문자 포함 | TRIM·CLEAN 조합 | =VALUE(TRIM(CLEAN(A2))) | 줄바꿈·비가시 문자 제거 후 숫자로 변환한다. |
| 특정 기호 제거 필요 | SUBSTITUTE | =VALUE(SUBSTITUTE(A2,",","")) | 천 단위 콤마를 제거하여 안정적으로 변환한다. |
예제 데이터 변환 파이프라인 원본(A열) → 정리(B열): =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(9),"")) → 숫자(C열): =NUMBERVALUE(B2, ".", ",") 주의 :
NUMBERVALUE(텍스트, 소수점, 구분기호)에서 소수점과 천 단위 구분기호를 반드시 구분해서 지정해야 한다. 동일 문자로 지정하면 오류가 발생한다.대량 자동화: ‘텍스트 나누기(구분 기호로 나누기)’ 활용
데이터 탭 → 텍스트 나누기 → 구분 기호 선택 없이 ‘마침’을 누르면 엑셀이 열의 데이터 유형을 추정하여 텍스트 숫자를 숫자로 변환한다. 소수점·날짜 해석에 주의하여 ‘열 데이터 형식’을 수동으로 ‘일반’ 또는 ‘텍스트/날짜’로 지정한다.
- 대량 CSV 열을 한 번에 정규화하기에 효율적이다.
- 앞자리 0이 중요한 품목코드 등은 ‘텍스트’로 강제 지정해야 한다.
수식 없이 즉시 변환: 1을 곱하기·0을 더하기·곱하기 붙여넣기
- 빈 셀에 숫자 1을 입력하고 복사한다.
- 대상 범위를 선택한다.
- 홈 → 붙여넣기 → 선택하여 붙여넣기 → 연산에서 ‘곱하기’를 선택한다.
같은 방식으로 0을 더해도 된다. 데이터 값 자체가 재계산되며 텍스트는 숫자로 변한다.
주의 : 이 방법은 숫자 형태를 강제하므로, 숫자처럼 보이지만 사실상 코드인 값(예:
00123)의 선행 0이 사라질 수 있다.작은따옴표 제거: 앞에 붙은 ' 처리
셀에 작은따옴표가 보이면 편집 모드에서 삭제하면 된다. 대량 처리 시는 다음 두 가지가 효율적이다.
- 찾기/바꾸기:
'→ 빈 값으로 바꾸기이다. - 수식:
=VALUE(SUBSTITUTE(A2,"'",""))이다.
불연속 공백(CHAR(160))·탭(CHAR(9)) 제거
웹·PDF에서 복사한 수치에는 비분리 공백이 자주 끼어 있다. 다음 수식으로 정리한다.
=NUMBERVALUE( TRIM( SUBSTITUTE( SUBSTITUTE(A2,CHAR(160)," "), CHAR(9),"" ) ), ".", "," )공백의 코드값을 확인하려면 =CODE(MID(A2,행번호,1))로 위치를 탐색한다.
날짜·시간 텍스트를 일·시분초 숫자로 변환
| 텍스트 예시 | 목표 | 수식 | 설명 |
|---|---|---|---|
| "2025-10-25" | 일련번호 | =DATEVALUE(A2) | 표시 형식을 날짜로 바꾸면 YYYY-MM-DD로 보인다. |
| "14:30:00" | 시간 값 | =TIMEVALUE(A2) | 당일 시각 비율(0~1)로 저장된다. |
| "2025.10.25 14:30" | 일시 값 | =DATEVALUE(LEFT(A2,10))+TIMEVALUE(RIGHT(A2,5)) | 구분자가 혼재되어도 분해 후 합산하면 안정적이다. |
주의 : 시스템 로캘과 다른 날짜 구분 기호를 가진 텍스트는
텍스트 나누기 마법사에서 열 형식을 ‘날짜(연-월-일)’ 등으로 명시 지정하는 것이 안전하다.로캘 혼재 데이터의 표준화 전략
다국적 보고서에서 1.234,56 같은 유럽식 표기가 들어올 때는 다음 절차가 안전하다.
SUBSTITUTE(A2,".","")로 천 단위 점을 제거한다.NUMBERVALUE(결과, ",", ".")로 소수점 콤마를 지정한다.
=NUMBERVALUE(SUBSTITUTE(A2,".",""), ",", ".")과학적 표기·긴 숫자(신용카드·바코드) 주의
자릿수가 15자리를 넘으면 엑셀은 유효자릿수 15로 반올림한다. 순수 숫자로 변환하면 원본이 훼손될 수 있다. 이런 값은 숫자 연산 대상이 아니라면 아예 텍스트로 유지한다.
주의 : 카드번호·바코드·대형 계좌번호는 텍스트 보존이 원칙이다. 계산이 필요한 경우에만 별도 키를 생성하여 숫자 변환을 진행한다.
파워 쿼리에서 일괄 변환
데이터 → 데이터 가져오기 → 텍스트/CSV에서 가져오기 → 미리 보기에서 각 열의 형식을 ‘정수’·‘소수’·‘날짜/시간’으로 지정한다. 로드 전 변환 단계에 값 바꾸기로 불필요 기호를 제거하고, 형식 변경으로 최종 숫자형으로 강제한다. 대량 파일에 재사용 가능하다.
파워 쿼리 변환 팁 1) 천 단위 기호 제거: 변환 → 값 바꾸기 → "," → 빈 값 2) 공백 제거: 변환 → 서식 → 양쪽 공백 제거 3) 데이터 형식: 열 머리글 아이콘 → 소수/정수/날짜 지정수식 결과를 값으로 고정하며 텍스트→숫자 정리
- B열에 정리 수식을 작성한다(예:
=NUMBERVALUE(TRIM(A2),".",",")). - B열 전체 선택 → 복사 → 마우스 오른쪽 → ‘값’으로 붙여넣기이다.
- 원본 A열을 제거하거나 보관한다.
실무 패턴별 추천 시나리오
| 패턴 | 증상 | 권장 방법 | 비고 |
|---|---|---|---|
| CSV 수입 직후 | 전체 열이 텍스트이다 | 텍스트 나누기 또는 파워쿼리 형식 지정이다 | 가져오기 단계에서 형식을 확정한다 |
| 웹 복사 자료 | 공백·비가시 문자 섞임이다 | TRIM+CLEAN+SUBSTITUTE 후 VALUE/NUMBERVALUE이다 | CHAR(160) 제거를 포함한다 |
| 유럽식 표기 | 콤마가 소수점이다 | NUMBERVALUE(텍스트, ",", ".")이다 | 점은 천 단위로 처리한다 |
| 부분 범위만 오류 | 초록 삼각형 표시이다 | 오류 검사 ‘숫자로 변환’이다 | 수동 선택 후 처리한다 |
| 즉시 강제 변환 | 수식 없이 값만 변환이다 | 선택하여 붙여넣기 → 곱하기(1)이다 | 코드값 선행 0 손실 주의이다 |
| 날짜·시간 혼재 | YYYY.MM.DD HH:MM이다 | LEFT/RIGHT 분해 + DATEVALUE/TIMEVALUE이다 | 표시 형식 날짜로 지정한다 |
VBA로 고급 정리(선택)
대량의 열에서 숨은 문자 제거와 숫자 변환을 일괄 처리해야 할 때 간단한 매크로가 유용하다.
Sub TextToNumberClean() Dim rng As Range Set rng = Selection Dim c As Range Application.ScreenUpdating = False For Each c In rng.Cells If Not IsEmpty(c.Value) Then Dim s As String s = CStr(c.Value) ' 비분리 공백·탭 제거 s = Replace(s, Chr(160), " ") s = Replace(s, Chr(9), "") s = WorksheetFunction.Trim(s) ' 작은따옴표 제거 If Left$(s, 1) = "'" Then s = Mid$(s, 2) ' 천 단위 콤마 제거 s = Replace(s, ",", "") ' 소수점은 점으로 가정 On Error Resume Next c.Value = CDbl(s) On Error GoTo 0 End If Next c Application.ScreenUpdating = True End Sub주의 : 다양한 로캘 소수점 기호를 동시에 처리하려면
Replace 규칙을 분기하거나 변환 표를 두고 단계적으로 치환해야 한다.검증 체크리스트
- 합계·평균이 기대값과 일치하는지 확인한다.
- 셀 맞춤이 기본 ‘오른쪽’ 정렬인지 확인한다(텍스트는 기본 왼쪽 정렬이다).
- 피벗테이블 값 영역에서 집계가 가능한지 확인한다.
- 숫자 서식 변경 시 표시가 정상적으로 변하는지 확인한다.
- 선행 0 보존이 필요한 열은 텍스트 유지 또는 사용자 지정 서식
000000을 쓴다.
현장 예제: 원본→정규화→숫자 변환 파이프라인
| 단계 | 예시 | 사용 수식/기능 | 결과 |
|---|---|---|---|
| 원본 | " 1,234.50" (CHAR(160) 포함) | — | 텍스트이다 |
| 정리 | 공백·탭 제거 | =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(9),"")) | "1,234.50"이다 |
| 숫자 변환 | 천 단위 제거 후 숫자 | =VALUE(SUBSTITUTE(B2,",","")) | 1234.5이다 |
실수 방지 포인트
- 서식만 바꾸지 말고 값 자체를 변환한다.
- 코드·식별자는 텍스트 유지가 맞다.
- CSV 가져오기 시 구분 기호와 소수점 로캘을 먼저 확인한다.
- 날짜/시간은 분해 후 합성 방식이 가장 안전하다.
자주 쓰는 변환 수식 모음
' 천 단위 콤마 제거 후 숫자 =VALUE(SUBSTITUTE(A2,",",""))
' 불연속 공백 제거 후 숫자
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))
' 유럽식 숫자
=NUMBERVALUE(A2, ",", ".")
' 날짜+시간 혼합
=DATEVALUE(LEFT(A2,10))+TIMEVALUE(RIGHT(A2,8))
' 제로 패딩 유지(텍스트 보존)
=TEXT(VALUE(A2),"000000") '연산용 숫자 별도 보관 권장
문제 해결 가이드(증상→원인→해결)
| 증상 | 가능 원인 | 해결 |
|---|---|---|
| 합계가 0 또는 비정상이다 | 텍스트 숫자 혼재이다 | VALUE/NUMBERVALUE 또는 붙여넣기 연산이다 |
| 초록 삼각형 경고 | 텍스트로 저장된 숫자이다 | 경고 옵션 ‘숫자로 변환’이다 |
| 표시와 계산이 다르다 | 서식만 숫자이다 | 값 변환 후 서식 적용이다 |
| 날짜가 뒤바뀐다 | 로캘 차이 또는 월/일 순서 혼동이다 | 텍스트 나누기에서 날짜 형식 명시이다 |
| 선행 0 사라짐 | 숫자 강제 변환이다 | 텍스트 유지 또는 사용자 지정 서식이다 |
FAQ
서식을 ‘숫자’로 바꿨는데도 합계가 계산되지 않는 이유는 무엇인가?
값의 데이터 유형이 여전히 텍스트이기 때문이다. VALUE·NUMBERVALUE 또는 붙여넣기 연산으로 값 자체를 숫자로 바꿔야 한다.
유럽식 수치와 한국식 수치가 섞여 있을 때 안전한 방법은 무엇인가?
NUMBERVALUE를 사용하여 소수점·천 단위 기호를 명시하고, 혼합된 경우에는 전처리로 SUBSTITUTE로 천 단위 기호를 제거한 뒤 일괄 변환한다.
품목코드처럼 선행 0을 유지하면서 숫자 연산도 하고 싶다. 방법은?
분리 저장이 정답이다. 연산용 숫자 열은 숫자로 변환하고, 표시용 코드는 텍스트로 보관하거나 TEXT(값,"000000")으로 별도 생성한다.
과학적 표기로 바뀌는 긴 숫자는 어떻게 다뤄야 하나?
유효자릿수 15 제한 때문에 손실이 발생하므로 텍스트로 유지한다. 계산이 필요하면 다른 키를 만들어 사용한다.
파워쿼리로 가져올 때부터 정확히 숫자로 만드는 요령은?
미리 보기에서 각 열의 데이터 형식을 지정하고, 필요 시 값 바꾸기로 구분 기호를 제거한 후 형식 변경을 적용한다. 동일 쿼리를 다른 파일에도 재사용한다.