엑셀 소수점 분리/표시 완전 가이드: 정수부·소수부 따로 표기, 고정 자릿수, 지역(,)·(.) 호환, TEXTSPLIT·TEXTAFTER 활용

Posted on by
엑셀 소수점 분리

엑셀 소수점 분리: 정수부·소수부 따로 표시하는 6가지 실무 레시피

엑셀 소수점 분리가 필요할 때, 가장 빠른 방법부터 구버전 호환·지역(소수점 기호 . 또는 ,) 호환까지 한 번에 정리합니다. 정수부/소수부를 숫자로 분리하거나, 소수부만 텍스트로 꺼내 2자리·N자리로 고정하는 공식, 표(구조화 참조) 자동 확장, 빈 셀·음수·오류까지 안전하게 다루는 팁을 담았습니다.

빠른 해결(Quick Fix)

① 정수부/소수부 숫자 분리 (Microsoft 365/2021 이상)

  1. 정수부(양수·음수 안전): =TRUNC(A2,0)
  2. 소수부(0 ≤ 값 < 1): =MOD(A2,1)
  3. 소수부만 텍스트(자릿수 보존형):
    =TEXTAFTER(TEXT(A2,"0.################"), ".")
    설명: 숫자를 텍스트로 바꿔 자릿수를 보존하고, 소수점 뒤만 추출합니다.

② 고정 자릿수 소수부(예: 2자리 “45” 형태)

=TEXT(ROUND(MOD(A2,1),2)*100,"00")

소수부를 100배(두 자리)로 변환해 0으로 채우고 텍스트로 반환합니다. 세 자리는 *1000,"000"처럼 확장합니다.

③ 구버전(2019 이하) 대체식

  • 소수부 텍스트(지역 자동 인식 x):
    =RIGHT(TEXT(A2,"0.############"), LEN(TEXT(A2,"0.############"))-FIND(".",TEXT(A2,"0.############")))
  • 지역(., ,) 호환형(자동 인식):
    =LET(d, MID(TEXT(1.1,"0.0"),2,1), RIGHT(TEXT(A2,"0"&d&"############"), LEN(TEXT(A2,"0"&d&"############"))-FIND(d, TEXT(A2,"0"&d&"############"))))

왜 이런 문제가 생기나?(개념·원리)

INT vs TRUNC vs MOD

  • TRUNC(n,0)은 소수부를 “절삭”(0쪽으로 잘라냄). TRUNC(-1.8,0)=-1
  • INT(n)은 “내림”(−∞ 방향). INT(-1.8)=-2. 음수에서 TRUNC와 차이.
  • MOD(n,1)은 소수부를 0~<1 범위로 반환(음수여도 결과는 0~1). MOD(-1.2,1)=0.8

지역 구분 기호(소수점 . vs ,) 호환

Excel은 Windows/Office 지역 설정에 따라 소수점 기호가 달라집니다. 텍스트 함수로 소수부를 꺼낼 때 “.”를 하드코딩하면 유럽형(,) 환경에서 깨질 수 있습니다. 다음처럼 소수점 기호를 자동 감지해 쓰면 안전합니다:

=LET(d, MID(TEXT(1.1,"0.0"),2,1),
 TEXTAFTER(TEXT(A2,"0"&d&"################"), d))

d에 현재 통합문서의 소수점 기호를 검출해 담고, 그 문자를 기준으로 분리합니다.

실무 예제

샘플 데이터

항목원본 값(A열)설명
양수123.456일반 케이스
음수-78.9INT/TRUNC 차이 주의
정수45소수부 없음
빈 칸오류 방지 필요
문자열“12.30”텍스트 숫자

1) 정수부/소수부 분리 — 숫자 그대로

정수부(B열): =IF(A2="","",TRUNC(A2,0))
소수부(C열): =IF(A2="","",MOD(A2,1))

표시 자리수는 셀 서식(Ctrl+1 → 표시 형식)에서 0.00 같은 사용자 지정으로 조절합니다.

2) 소수부만 텍스트로(자릿수 보존/분석용)

소수부를 텍스트로 뽑으면 “0.3000”처럼 원래 자릿수를 유지해 비교, 라벨, 코드화에 유용합니다.

=IF(A2="","",
 TEXTAFTER(TEXT(A2,"0.################"), "."))

정수(45)는 소수점 뒤가 없으므로 빈 문자열을 반환합니다. 필요 시 IF(= "", "00") 같이 기본값을 채우세요.

3) 2·3·N자리 고정 소수부(앞쪽 0 채우기)

두 자리(“05”, “30”):

=IF(A2="","",
 TEXT(ROUND(MOD(A2,1),2)*100,"00"))

N자리 일반식: =TEXT(ROUND(MOD(A2,1),N)*10^N, REPT("0",N))

4) TEXTSPLIT으로 한 번에 분해(365/2021+)

=LET(
 d, MID(TEXT(1.1,"0.0"),2,1),
 SPLIT, TEXTSPLIT(TEXT(A2,"0"&d&"################"), d),
 H, INDEX(SPLIT,1),  T, IF(COUNTA(SPLIT)=2, INDEX(SPLIT,2), ""),
 H & " | " & T)

결과 예: 123 | 456. 배열이므로 옆 셀로 쏟아내려면 TEXTSPLIT(...) 자체를 입력한 뒤 스필된 첫 셀과 두 번째 셀을 각각 참조합니다.

5) 표(Structured Reference)로 자동 확장

데이터를 표(단축키 Ctrl+T)로 변환하고, 열 이름을 [Value]라면:

=LET(d, MID(TEXT(1.1,"0.0"),2,1),
 TEXTAFTER(TEXT([@Value],"0"&d&"################"), d))

행이 늘어도 자동으로 범위가 확장됩니다.

6) 보고서 안전장치(IFERROR/빈셀이나 문자 방지)

  • 검증 포함: =IFERROR(IF(A2="","",TEXTAFTER(TEXT(A2,"0.################"),".")),"")
  • 문자열 숫자 강제 변환: =VALUE(A2)를 중간에 넣어 “12.30” 같은 텍스트 숫자를 숫자로 바꾼 뒤 처리합니다.

대체 방법/주의/체크리스트

  • 음수는 TRUNC와 INT가 다릅니다. 절삭은 TRUNC, 내림은 INT.
  • 라벨/코드용이면 텍스트로 추출, 계산용이면 숫자로 분리하세요.
  • 보고서 일관성을 위해 N자리 고정이 필요하면 TEXT( ... , REPT("0",N)) 패턴을 쓰세요.
  • 지역(., ,) 이슈가 있으면 LET(d, MID(TEXT(1.1,"0.0"),2,1), ...)로 소수점 기호를 감지해 쓰면 안전합니다.
  • 서식만으로는 다른 셀에 소수부를 뽑아낼 수 없습니다. 셀 간 분리는 함수로 처리하세요.

Troubleshooting

증상원인해결법
음수에서 정수부가 기대와 다름 INT 사용(내림) TRUNC로 절삭 또는 정책에 맞게 선택
유럽형(,) PC에서 공식 실패 소수점 기호를 “.”로 하드코딩 LET(d, MID(TEXT(1.1,"0.0"),2,1), ... d ...) 패턴으로 자동 감지
정수 값에서 빈칸 대신 0 표시 기본 반환값 미지정 IF(= "", "00") 등 기본 문자열 지정
“12.30” 같은 텍스트 숫자 미인식 숫자 서식 아님 VALUE로 숫자 변환 후 처리
자릿수 통일 안 됨 고정 자릿수 서식 미사용 TEXT(...,REPT("0",N))로 0-padding

마무리: 관련 글 추천

추가 내부 링크: 엑셀 파일 용량 줄이는 법, 워크시트 단축키

엑셀 RANK 함수 완벽 가이드: 동점·그룹·가시행·상위 N까지

Leave a Reply

Your email address will not be published. Required fields are marked *