이 글의 목적은 엑셀에서 품목코드나 ID가 원치 않게 날짜로 바뀌는 현상을 근본적으로 차단하고, 데이터 유입 단계별로 재현 가능한 표준 절차를 제시하여 실무에서 즉시 적용 가능하게 하는 것이다.
왜 엑셀이 숫자·기호를 날짜로 바꾸는가
엑셀은 셀 서식이 일반일 때 패턴을 추정하여 자동으로 데이터 유형을 지정하려고 시도한다. 예를 들어 3-2는 3월 2일, 1/2는 1월 2일로 인식할 수 있다. CSV를 더블클릭으로 열거나 외부 데이터 원본을 붙여넣을 때도 동일한 추정이 동작한다. 따라서 입력 전·가져오기 전 단계에서 텍스트로 고정하는 전략이 핵심이다.
핵심 전략 요약
| 상황 | 문제 예 | 최적 해법 | 대체 해법 |
|---|---|---|---|
| 셀에 직접 입력 | 3-2 입력 시 3월 2일로 변환 | 사전 서식: 텍스트(숫자 탭 → 텍스트) | 선행 아포스트로피 '3-2, 사용자 지정 서식 |
| 다른 프로그램에서 붙여넣기 | 부서코드 01-02가 날짜로 바뀜 | 붙여넣기 대상 범위를 먼저 텍스트로 서식 지정 | 붙여넣기 옵션에서 값만, 이후 데이터 > 텍스트 나누기로 텍스트 고정 |
| CSV 파일 더블클릭으로 열기 | 1-1, 1/2, 1E10 왜곡 | 데이터 > 텍스트/CSV에서 가져오기 후 열 유형을 텍스트로 지정 | CSV 대신 TXT로 저장 후 텍스트 가져오기 마법사 사용, 파워쿼리 형식 탐지 끄기 |
| 파워쿼리(데이터 변환) | 자동 유형 감지로 날짜/숫자 강제 변환 | 유형 탐지 끄기, 각 열을 텍스트로 지정 | 쿼리 단계에서 Changed Type 제거 |
| 대량 업로드용 템플릿 관리 | 선행 0이 사라짐(예: 00123) | XLSX 템플릿에 열을 텍스트로 고정하여 배포 | CSV 값에 ="00123" 포맷 사용 |
주의 : 파일을 열거나 붙여넣은 후 수정하는 방식은 이미 변형된 데이터를 원상복구하지 못하는 경우가 많다. 데이터가 엑셀에 들어오기 전 단계에서 텍스트로 확정하는 절차를 우선한다.
방법 1: 입력 전 셀 서식을 텍스트로 지정
직접 입력 또는 붙여넣기 전에 대상 범위를 선택하고 텍스트로 고정한다. 이 방법은 가장 단순하며 재현성이 높다.
- 대상 범위 선택 또는 열 머리글 클릭으로 전체 열 선택한다.
홈 > 숫자 그룹에서 드롭다운을 텍스트로 변경한다.- 이제
3-2,01-02,1/2등을 입력해도 그대로 텍스트로 남는다.
키보드만으로 빠르게 설정하려면 다음을 활용한다.
Alt, H, N, T 주의 : 서식을 텍스트로 바꾸기 전에 이미 입력된 셀은 변환되지 않는다. 기존 값은 다시 편집(Enter 재입력)하거나
텍스트 나누기 등을 사용하여 텍스트화한다.방법 2: 선행 아포스트로피로 즉시 텍스트화
한두 건의 예외 입력에는 선행 아포스트로피를 붙인다.
'3-2 '1/2 '00123 표시 시 아포스트로피는 보이지 않고 내용만 텍스트로 저장된다. 대량 처리에는 비효율적이므로 템플릿 서식과 병행한다.
방법 3: CSV를 더블클릭하지 말고 가져오기 마법사 사용
CSV를 더블클릭하면 전체 열이 일반 서식으로 열리고 자동 유형 추정이 적용된다. 데이터 탭에서 가져오기를 사용하여 각 열을 텍스트로 강제한다.
데이터 > 텍스트/CSV에서를 클릭한다.- CSV 파일 선택 후 미리보기 창에서 구분 기호를 확인한다.
- 로드 옆의 화살표를 눌러 데이터 변환을 선택한다.
- 파워쿼리 창에서 문제 열을 선택하고
데이터 유형을 텍스트로 지정한다. - 닫기 및 로드로 시트에 로드한다.
주의 : 가져오기 후에도 열 머리글 위에 작은 아이콘으로 데이터 유형이 표시된다. 날짜·숫자로 표시되면 다시 텍스트로 변경한다.
방법 4: TXT로 변환하여 고전 텍스트 가져오기 마법사 사용
버전과 옵션에 따라 CSV 가져오기 인터페이스가 단순할 수 있다. 이때 CSV를 .txt로 저장하고 텍스트 가져오기 마법사를 활용한다.
- CSV를 메모장으로 열어 다른 이름으로 저장에서 확장자를
.txt로 저장한다. - 엑셀에서
데이터 > 텍스트/CSV에서또는데이터 > 가져오기를 통해 TXT를 선택한다. - 구분 기호(쉼표 또는 탭)를 지정하고 데이터 형식에서 해당 열을 텍스트로 설정한다.
방법 5: 파워쿼리에서 형식 탐지 끄기
파워쿼리는 기본적으로 Changed Type 단계를 자동 추가하여 날짜나 숫자로 바꾼다. 다음 중 하나를 선택한다.
- 자동 유형 감지 해제를 사용한다. 쿼리 옵션에서 유형 감지를 없음으로 설정한다.
Applied Steps에서Changed Type단계를 삭제한다.- 각 열 머리글의 형식 드롭다운에서 텍스트로 명시한다.
파워쿼리 M 코드에서 텍스트 지정을 명시하려면 다음과 같이 사용한다.
// 예시: 첫 번째, 두 번째 열을 텍스트로 강제 Table.TransformColumnTypes(Source, {{"PartNo", type text}, {"Batch", type text}}, "ko-KR") 방법 6: 붙여넣기 전에 열을 텍스트로 고정
웹, ERP, 메모장 등에서 복사한 데이터를 붙여넣을 때는 대상 열을 미리 텍스트로 지정한다. 이미 변환된 경우 텍스트 나누기를 활용한다.
- 붙여넣기 대상 열을 선택하고 텍스트 서식으로 바꾼다.
- 값 붙여넣기(
Ctrl+Alt+V→ 값)를 사용한다. - 필요 시
데이터 > 텍스트 나누기를 열어 구분 기호를 지정하고 각 열 데이터 형식을 텍스트로 선택한다.
주의 : 붙여넣기 후 변환은 되돌리기 어렵다. 텍스트 서식 고정이 최우선이다.
방법 7: 사용자 지정 서식으로 표시만 고정하는 경우
표시만 유지하고 내부 값은 숫자로 두어야 하는 요구가 있을 수 있다. 예를 들어 00123을 숫자 123으로 보관하면서 화면에만 5자리로 보이게 하려면 사용자 지정 서식을 사용한다.
서식 > 셀 서식 > 표시 형식 > 사용자 지정 > 유형: 00000 단, 이 방법은 3-2처럼 기호가 포함된 코드에는 부적합하다. 그러한 값은 반드시 텍스트이어야 한다.
방법 8: TEXT 함수로 텍스트 고정
수식으로 원본을 변환할 때는 TEXT 함수를 사용한다.
=TEXT(A2,"@") 이 방식은 계산 열을 하나 더 두므로, 완료 후 복사하여 값으로 붙여넣어 최종 텍스트를 확정한다.
방법 9: CSV 생성 시 안전 포맷 사용
업스트림 시스템에서 CSV를 생성할 수 있다면 엑셀의 자동 변환을 고려한 방어적 포맷을 적용한다.
| 원 데이터 | CSV 안전 표현 | 설명 |
|---|---|---|
| 00123 | ="00123" | 수식 평가를 통해 텍스트 00123으로 고정한다. |
| 3-2 | '3-2 | 선행 아포스트로피로 텍스트 강제한다. |
| 1E10 | ="1E10" | 지수 표기로 바뀌는 것을 방지한다. |
CSV 예시는 다음과 같다.
PartNo,Batch,Note ='3-2',="00123","keep as text" 주의 : 일부 시스템은 선행 아포스트로피나 수식 형식을 허용하지 않는다. 이 경우 XLSX 템플릿 배포나 파워쿼리에서 열 유형 지정이 더 안전하다.
방법 10: VBA로 대상 열을 텍스트로 잠그기
반복 작업이라면 매크로로 대상 열의 표시 형식을 텍스트로 고정한다.
Sub LockAsText() With ThisWorkbook.Worksheets("입력").Range("A:C") .NumberFormat = "@" End With End Sub 파일 열 때 자동 적용하려면 다음을 사용한다.
Private Sub Workbook_Open() With Worksheets("입력").Range("A:C") .NumberFormat = "@" End With End Sub 주의 : 이미 날짜로 변환된 값은 서식 변경만으로 되돌아오지 않는다. 매크로는 데이터 유입 전에 실행해야 한다.
현장에서 자주 발생하는 패턴과 처방
| 패턴 | 예시 | 결과(문제) | 처방 |
|---|---|---|---|
| 하이픈 포함 코드 | AB-01-02, 3-2 | 날짜로 자동 변환 | 열을 텍스트로 선지정 또는 파워쿼리에서 텍스트로 강제 |
| 슬래시 포함 코드 | 1/2, 5/10A | 날짜 인식 또는 분수화 | 텍스트 서식, CSV 생성 시 ="1/2" |
| 선행 0 유지 | 00123, 0007 | 0 손실 | 텍스트 서식 또는 사용자 지정 서식 00000 |
| 지수 표기 오인 | 1E10, 2e5 | 1.00E+10으로 표시 | ="1E10" 또는 텍스트 서식 |
| 긴 숫자 ID | 16자리 이상 | 정밀도 손실 | 반드시 텍스트로 저장, 파워쿼리 텍스트 강제 |
대량 데이터 교정 절차(체크리스트)
- 유입 경로 확인: 수기 입력, 붙여넣기, CSV/TXT, ODBC/파워쿼리 중 무엇인지 식별한다.
- 사전 고정: 입력 열을 텍스트로 지정하거나 파워쿼리에서 텍스트로 강제한다.
- 샘플 검증: 대표 문제값(하이픈, 슬래시, 선행 0, 긴 숫자)을 테스트한다.
- 대량 처리: 전량 로드 또는 붙여넣기를 수행한다.
- 사후 점검: 변환 건수, 선행 0 유지 여부, 지수 표기 발생 여부를 필터로 확인한다.
- 고정화: 필요 시 수식 결과를 값으로 붙여넣어 확정한다.
- 템플릿화: 성공 설정을 XLSX 템플릿, 쿼리 파일로 저장하여 재사용한다.
품질 보증을 위한 검사용 수식
날짜로 변환된 셀을 탐지하기 위한 간단한 테스트를 사용한다.
=SUMPRODUCT(--ISNUMBER(A2:A1000)) 의도한 모든 값이 텍스트라면 위 결과는 0이어야 한다. 또 다른 검사는 다음과 같다.
=SUMPRODUCT(--(CELL("format",A2:A1000)="G")) // 일반 서식 탐지 =SUMPRODUCT(--(ISTEXT(A2:A1000))) // 텍스트 여부 템플릿 배포 전략
- 업로드 양식은 XLSX로 배포하고, 텍스트 열을 미리 지정한다.
- 검증 시트에 검사용 수식을 포함하여 사용자가 즉시 상태를 확인하게 한다.
- 파워쿼리 연결 파일은 Changed Type 제거 상태로 공유한다.
자동화 예시: 붙여넣기 전 텍스트 고정 매크로
Sub PasteAsTextToColumns() Dim tgt As Range Set tgt = Application.InputBox("붙여넣을 시작 셀을 선택:", Type:=8) If tgt Is Nothing Then Exit Sub With tgt.CurrentRegion .NumberFormat = "@" End With tgt.PasteSpecial xlPasteValues End Sub 복구 요령: 이미 날짜로 변환된 값을 되살리는가
엑셀이 3-2를 3월 2일로 바꿔 저장한 뒤에는 원문을 추정하기 어렵다. 데이터가 단순 날짜 범위가 아닐 경우 복구는 불가능에 가깝다. 가능하다면 원본 파일에서 다시 가져오고, 앞으로는 사전 고정 절차를 준수한다.
현장 적용 시나리오
시나리오 A: 공사 자재코드 AB-01-02가 날짜로 바뀌는 문제를 해결하려면, 템플릿 열을 텍스트로 고정하고 CSV는 파워쿼리로 가져오며 유형 탐지를 끈다. 초기 샘플 20건을 검증하고 로드한다.
시나리오 B: 긴 계측기 ID 18자리가 지수 표기로 열릴 때, TXT로 가져오기 또는 파워쿼리에서 텍스트로 강제한다. 사후 검사용 수식으로 숫자형 혼입을 0건으로 확인한다.
시나리오 C: 외주사가 CSV만 제공할 때, 값 중 선행 0 보존이 필요하면 ="00123" 포맷 허용을 요청하거나 XLSX 양식 배포로 전환한다.
FAQ
시스템에서 CSV만 허용하고 수식 형태(="00123")를 금지한다면 어떻게 하나?
CSV를 TXT로 변환하여 텍스트 가져오기 마법사 또는 파워쿼리로 텍스트 강제 후 로드한다. 또는 최종 수신 파일은 CSV이되 내부 생산·검증 과정은 XLSX로 전환하여 형식 안전성을 확보한다.
엑셀 옵션에서 자동 변환을 완전히 끌 수 있는가?
일반 입력과 CSV 더블클릭 열기에 대한 전역 OFF 스위치는 제공하지 않는다. 사전 서식 지정, 가져오기 절차, 파워쿼리 설정으로 제어한다.
이미 날짜로 바뀐 값을 일괄 되돌릴 수 있는가?
원문 규칙을 알고 있으면 텍스트로 역변환하는 사용자 정의 수식으로 일부 복원 가능하나 신뢰성이 낮다. 원본 파일에서 재가져오는 것이 안전하다.
구글 스프레드시트로 우회하면 해결되는가?
스프레드시트도 자동 추정이 존재한다. 가져오기 단계에서 텍스트 지정이 필요하며 원리는 동일하다.
선행 0을 유지하면서 숫자 연산도 하고 싶다. 방법이 있는가?
표시만 0을 채우려면 사용자 지정 서식 00000을 사용하고 내부는 숫자를 유지한다. 단, 하이픈·슬래시는 텍스트로 관리해야 한다.