이 글의 목적은 엑셀에서 16진수·8진수 변환 시 발생하는 #NUM!, #VALUE!, #NAME? 등의 오류 원인을 정확히 진단하고, 함수 설정과 데이터 전처리, 대체 수식·VBA·파워쿼리까지 포함한 실무 중심의 해결 방법을 단계별로 제시하는 것이다.
엑셀에서 사용하는 진법 변환 핵심 함수 개요
엑셀은 기본적으로 다음 함수를 제공하여 진법 변환을 수행한다.
HEX2DEC(hex_text): 16진수 텍스트를 10진수로 변환한다.DEC2HEX(number, [places]): 10진수 정수를 16진수 텍스트로 변환한다.HEX2OCT(hex_text, [places])/OCT2HEX(oct_text, [places]): 16↔8진 변환을 수행한다.OCT2DEC(oct_text)/DEC2OCT(number, [places]): 8↔10진 변환을 수행한다.BASE(number, radix, [min_length]): 10진수 정수를 임의의 진법(2~36) 문자열로 변환한다.DECIMAL(text, radix): 임의의 진법 문자열을 10진수로 변환한다.
주의 : 위 함수들은 결과를 텍스트로 반환하는 경우가 많다. 후속 계산이 필요하면
VALUE() 또는 산술 연산(+0)으로 숫자형으로 변환해야 한다.오류 유형별 원인과 즉시 조치
| 오류 코드 | 대표 상황 | 원인 | 즉시 조치 |
|---|---|---|---|
| #NUM! | DEC2HEX, DEC2OCT 변환 실패 | 함수 허용 범위를 벗어난 값 또는 [places]가 너무 작아 결과가 잘리는 경우이다. | 값을 범위 내로 조정하거나 [places]를 충분히 크게 지정한다. 음수의 경우 2의 보수 처리 규칙을 이해하고 입력한다. |
| #VALUE! | HEX2DEC/DECIMAL 입력 인식 실패 | 공백, 비허용 문자, 접두사 0x·&H 포함, 비가시 문자, 줄바꿈 등이 포함된 경우이다. | 전처리로 공백·비가시 문자를 제거하고 접두사를 삭제한다. TRIM, CLEAN, SUBSTITUTE를 사용한다. |
| #NAME? | 함수 자체가 인식되지 않음 | 오타, Excel 버전 미지원, 추가기능 비활성화 또는 지역 설정 문제이다. | 정확한 함수명으로 수정하고 버전 지원 여부를 확인한다. BASE/DECIMAL은 최신 버전에서 지원한다. |
| 잘못된 결과 | 음수 16진 인식 혼동 | 10문자 16진을 2의 보수 음수로 해석해야 하는데 부호 없는 값으로 처리한 경우이다. | 해석 규칙을 반영해 변환한다. 필요 시 사용자 정의 수식 또는 Power Query로 명시적으로 처리한다. |
함수 입력 허용 범위와 2의 보수 규칙
엑셀의 전통 진법 함수는 내부적으로 고정 폭 표현을 따른다. 범위를 벗어나면 #NUM! 오류가 발생한다.
| 함수 | 입력 허용 범위 | 출력 길이 기본 | 비고 |
|---|---|---|---|
| DEC2HEX | -549,755,813,888 ~ 549,755,813,887 | 음수는 10자리 16진(2의 보수) | [places]를 지정하면 왼쪽 0패딩한다. 너무 작으면 #NUM!이다. |
| HEX2DEC | 최대 10자리 16진 | — | 10자리이고 최상위 비트가 1이면 음수로 해석한다. |
| DEC2OCT | -536,870,912 ~ 536,870,911 | 음수는 10자리 8진(2의 보수) | 출력은 텍스트이다. |
| OCT2DEC | 최대 10자리 8진 | — | 10자리 8진 최상위 비트가 1이면 음수로 해석한다. |
| BASE | 정수만, 범위는 시스템 정수 한계 내 | [min_length]로 0패딩 | 2~36진 임의 변환을 지원한다. |
| DECIMAL | 문자열 길이 한계 내 | — | 2~36진 문자열을 10진수로 변환한다. |
주의 : 위 범위는 엑셀 전통 공학 함수의 정의에 따른다. 매우 큰 정밀도가 필요한 경우에는 Power Query 또는 외부 라이브러리를 활용하는 것이 안전하다.
전처리 체크리스트: 변환 전 입력값 청소
- 양끝·중간 공백 제거:
=SUBSTITUTE(TRIM(A2)," ","")로 공백 제거한다. - 숨은 제어문자 제거:
=CLEAN(A2)또는=SUBSTITUTE(A2,CHAR(160),"")로 제거한다. - 접두사 제거:
0x,0X,&H,h등의 접두·접미를 없앤다.=LET(s,UPPER(TRIM(A2)), s1,IF(LEFT(s,2)="0X",MID(s,3,99),s), s2,IF(LEFT(s1,2)="&H",MID(s1,3,99),s1), s3,IF(RIGHT(s2,1)="H",LEFT(s2,LEN(s2)-1),s2), s3) - 허용 문자 검증: 16진은
0-9A-F, 8진은0-7만 허용한다.=LET(s,UPPER(B2), ok,IFERROR(SUM(--ISNUMBER(SEARCH(MID(s,SEQUENCE(LEN(s)),1),"0123456789ABCDEF")))=LEN(s),FALSE), IF(ok,s,""))
16진→10진 변환이 실패할 때(HEX2DEC #VALUE!)
가장 흔한 원인은 입력이 텍스트가 아니거나 비허용 문자가 섞인 경우이다. 다음 절차로 해결한다.
- 전처리로 접두·공백 제거 후
HEX2DEC에 전달한다.=HEX2DEC( 전처리_함수(A2) ) - 숫자형으로 강제 변환이 필요하면
+0을 덧셈한다.=HEX2DEC( 전처리_함수(A2) )+0 - 입력이 음수의 2의 보수 표현인지 확인한다. 10자리 16진이고 첫 글자가 8~F이면 음수로 해석한다.
10진→16진 변환이 실패할 때(DEC2HEX #NUM!)
두 가지가 핵심이다. 허용 범위와 [places] 길이이다.
- 허용 범위를 벗어나면 #NUM!이다. 상한·하한을 나누어 검증한다.
=IF(OR(A2>549755813887,A2<-549755813888),"범위초과",DEC2HEX(A2)) - 결과 길이가
[places]보다 길면 #NUM!이다. 예상 자리수로 충분히 크게 지정한다.=DEC2HEX(A2, 16) '최대 16자리로 0패딩 - 음수는 10자리 2의 보수 문자열로 반환한다. 사람이 읽기 쉬운 부호·절대값 표현이 필요하면 후처리한다.
16진↔8진 변환에서의 흔한 실수
HEX2OCT와 OCT2HEX는 중간 10진 변환 없이 바로 사용할 수 있으나, 범위는 여전히 제한된다. 안전한 절차는 다음과 같다.
- 입력을 철저히 전처리하고 허용 문자 집합을 검증한다.
- 중간 검증을 위해
HEX2DEC또는OCT2DEC로 10진수를 만든 뒤,DEC2OCT또는DEC2HEX로 재변환한다.=LET(d,HEX2DEC( 전처리_함수(A2) ), IFERROR(DEC2OCT(d, [places]), "범위초과"))
BASE/DECIMAL을 이용한 범용 변환 파이프라인
BASE와 DECIMAL의 조합은 버전이 허용되는 환경에서 가장 견고하다.
- 任의 진법→10진:
=DECIMAL(정제텍스트, 기수) - 10진→任의 진법:
=BASE(정수, 기수, [최소길이])
예시: 16진 문자열 A2를 8진으로 바꾸되 8자리 0패딩한다.
=BASE( DECIMAL( 전처리_함수(A2), 16 ), 8, 8)주의 :
BASE와 DECIMAL은 음수 처리에서 전통 함수와 동작이 다를 수 있다. 일관성을 원하면 하나의 패밀리로 통일하여 사용한다.대량 데이터 처리용 구조화 템플릿
대량 변환을 위해 다음과 같은 구조를 추천한다.
| 열 | 내용 | 예시 수식 |
|---|---|---|
| A | 원본 문자열 | FF FE 01 |
| B | 정제 문자열 | =전처리_함수(A2) |
| C | 검증 플래그 | =IF(B2="","NG","OK") |
| D | 10진수 | =IF(C2="OK",HEX2DEC(B2),"") |
| E | 8진수 | =IF(D2="","",DEC2OCT(D2,8)) |
음수 2의 보수 표현 정확히 이해하기
엑셀의 DEC2HEX 음수 결과는 10자리 16진 2의 보수다. 예를 들어 -1은 FFFFFFFFFF로 나타난다. 이를 사람이 읽는 부호형으로 재해석하려면 다음과 같이 처리한다.
=LET(h,UPPER(B2), n,IF(LEN(h)=10,HEX2DEC(h),HEX2DEC(h)), signed,IF(AND(LEN(h)=10,LEFT(h,1)>="8"), HEX2DEC(h)-2^40, HEX2DEC(h)), signed)위 수식은 10자리 16진을 40비트 정수로 보고, 최상위 비트가 1이면 2^40을 빼서 음수로 환원한다.
CSV·텍스트 가져오기 시 선행 처리
외부 파일에서 16진·8진 문자열을 가져올 때 자동 형식 지정으로 인해 값이 변형될 수 있다. 다음 방법으로 보호한다.
- 텍스트 가져오기 마법사 또는 Power Query에서 열 형식을 텍스트로 지정한다.
- 앞서 설명한 전처리와 검증을 적용한다.
- 선행 0을 유지하려면 텍스트 형식과
[places]옵션을 병행한다.
Power Query(M)로 대용량·고정밀 처리
Power Query는 기수 변환을 내장한다. 열 추가에서 사용자 정의 열로 다음을 사용할 수 있다.
// 16진 텍스트 열 [Hex] → 10진 수 = Number.FromText([Hex], 16)
// 10진 열 [Dec] → 16진 텍스트(대문자)
= Text.Upper( Number.ToText([Dec], "X") )
// 10진 → 8진
= Number.ToText( Number.From([Dec]), "O" )
주의 : 매우 큰 정수는 기본 숫자형 정밀도를 초과할 수 있다. 이 경우 텍스트로 유지하여 이후 시스템에서 처리하거나, 단계적으로 나누어 변환하는 전략을 사용한다.
VBA로 강건한 변환 유틸리티 구성
엑셀 버전 제약이 있거나 정교한 예외 처리가 필요하면 VBA 사용자 정의 함수를 사용한다.
' 모듈: Hex/Oct 변환 유틸 Option Explicit
Public Function HexToDecClean(ByVal s As String) As Variant
Dim t As String
t = UCase$(Trim$(s))
If Left$(t, 2) = "0X" Then t = Mid$(t, 3)
If Left$(t, 2) = "&H" Then t = Mid$(t, 3)
If Right$(t, 1) = "H" Then t = Left$(t, Len(t) - 1)
t = Replace(t, " ", "")
If t = "" Then HexToDecClean = CVErr(xlErrValue): Exit Function
' 허용 문자 확인
Dim i As Long, ch As String
For i = 1 To Len(t)
ch = Mid$(t, i, 1)
If InStr(1, "0123456789ABCDEF", ch, vbBinaryCompare) = 0 Then
HexToDecClean = CVErr(xlErrValue): Exit Function
End If
Next
' WorksheetFunction로 범위 넓게 처리
HexToDecClean = Application.WorksheetFunction.Hex2Dec(t)
End Function
Public Function DecToHexSafe(ByVal n As Variant, Optional ByVal places As Variant) As Variant
On Error GoTo EH
If IsMissing(places) Or IsEmpty(places) Then
DecToHexSafe = Application.WorksheetFunction.Dec2Hex(n)
Else
DecToHexSafe = Application.WorksheetFunction.Dec2Hex(n, places)
End If
Exit Function
EH:
DecToHexSafe = CVErr(xlErrNum)
End Function
VBA를 사용할 때 CLng("&H" & s) 방법은 32비트 한계로 인해 큰 값에서 오버플로가 발생한다. 위와 같이 WorksheetFunction.Hex2Dec을 래핑하는 편이 안정적이다.
동적 배열과 누적 변환 파이프라인
여러 열에 걸친 일괄 변환을 동적 배열로 구성한다.
=LET(r,TAKE(A2:A1000,COUNTIF(A2:A1000,"<>")), clean,MAP(r,LAMBDA(x, 전처리_함수(x))), dec,MAP(clean,LAMBDA(x, IF(x="",,HEX2DEC(x)))), HSTACK(r,clean,dec, IF(dec="","",BASE(dec,8,8)), IF(dec="","",BASE(dec,16,8))))위 수식은 원본, 정제, 10진, 8진, 16진을 한 번에 산출하고 누락은 공란으로 유지한다.
테스트 케이스 세트로 회귀 검증하기
변환 로직 변경 시 기존 데이터가 망가지지 않도록 사전에 표준 테스트 케이스를 만들어둔다.
| 입력 | 예상 10진 | 예상 8진 | 예상 16진 | 비고 |
|---|---|---|---|---|
| FF | 255 | 00000377 | 000000FF | 대문자·0패딩 |
| 0x1A | 26 | 00000032 | 0000001A | 접두사 제거 |
| &H7F | 127 | 00000177 | 0000007F | VB 접두사 |
| FFFFFFFFFF | -1 | 7777777777 | FFFFFFFFFF | 2의 보수 음수 |
| 00000000 | 0 | 00000000 | 00000000 | 영 값 |
| 089 | — | — | — | 8진 불가, 오류 검출 |
실무 시나리오별 빠른 처방
시나리오 1) 16진 리스트를 8진으로 일괄 변환
B2에 정제 수식,C2에 10진,D2에 8진을 둔다.- 수식:
B2: =전처리_함수(A2) C2: =IFERROR(HEX2DEC(B2),"") D2: =IF(C2="","",BASE(C2,8,8))
시나리오 2) 음수 여부가 섞인 16진을 10진 부호형으로
=LET(h,UPPER(전처리_함수(A2)), d,HEX2DEC(h), IF(AND(LEN(h)=10,LEFT(h,1)>="8"), d-2^40, d))시나리오 3) #NUM! 발생을 사전 차단
=LET(n,A2, IF(OR(n<-549755813888, n>549755813887),"범위초과", DEC2HEX(n,16)))품질 보증을 위한 데이터 유효성 검사 규칙
- 16진 입력열에 사용자 지정 데이터 유효성:
=SUM(--ISNUMBER(SEARCH(MID(UPPER(A2),SEQUENCE(LEN(A2)),1),"0123456789ABCDEF")))=LEN(A2)를 TRUE로 강제한다. - 8진 입력열에는
=COUNT(FIND(MID(A2,SEQUENCE(LEN(A2)),1),"01234567"))=LEN(A2)을 적용한다.
문자열 길이와 0패딩 정책
다운스트림 시스템이 고정 길이를 요구하면 다음을 사용한다.
=TEXT(, )는 숫자형 포맷 지정이므로 진법 문자열에는 부적합하다. =RIGHT(REPT("0", 원하는길이) & 대상텍스트, 원하는길이)예시: 16진을 항상 8자리로
=RIGHT(REPT("0",8) & UPPER(BASE(DECIMAL(전처리_함수(A2),16),16)), 8)지역 설정과 함수 인식 문제 해결
- 리스트 구분자가 세미콜론인 환경에서는 인수를
;로 구분한다. - 함수명이 인식되지 않으면 오타 여부와 버전을 확인하고, 동일 목적의 대체 함수(BASE/DECIMAL)를 사용한다.
디버깅 루틴: 어디서 실패하는지 즉시 확인
각 단계 결과를 열로 분리하여 어느 단계에서 빈값 또는 오류가 나는지 가시화한다. IFERROR로 오류 메시지를 커스터마이즈해 원인을 바로 파악한다.
=IFERROR(HEX2DEC(B2), "HEX2DEC_실패") =IFERROR(DEC2OCT(C2,8), "DEC2OCT_실패")관리 기준서에 넣을 표준 운영 규칙
- 모든 외부 16진·8진 입력은 텍스트로 유지한다.
- 전처리 함수로 접두·공백·비허용 문자를 제거한다.
- 검증 후에만 변환을 수행한다.
- 음수 처리 정책(2의 보수)을 문서화한다.
- 고정 길이 요구 시 0패딩 규칙을 고정한다.
- 월 1회 테스트 케이스로 회귀 검증을 수행한다.
FAQ
HEX2DEC 결과가 텍스트라서 수식이 계산되지 않는다. 어떻게 숫자로 바꾸나?
+0을 더하거나 VALUE()로 감싸면 숫자형으로 변환된다.
“0x”, “&H” 같은 접두사가 섞여 있다. 일괄 처리 방법이 있나?
전처리 함수를 만들어 MAP/SCAN으로 전체 범위에 적용한다. 글 상단의 전처리 예제를 사용한다.
음수 16진을 사람이 읽는 부호로 보고 싶다. 방법이 있나?
10자리 16진이면 40비트 2의 보수로 보고 -2^40을 더해 복원한다. 전용 수식을 사용한다.
DEC2HEX에서 [places]를 작게 주면 왜 오류가 나나?
결과가 지정 자리수를 초과하면 #NUM!이 발생한다. 충분히 큰 자리수를 사용한다.
아주 큰 정수는 어떻게 처리하나?
Power Query의 Number.FromText(text, radix)와 Number.ToText를 사용하거나 외부 고정소수 라이브러리를 사용한다. 엑셀 전통 함수는 고정 폭 제한이 있다.