CONTENTS

Excel Decimal Separation: 6 Practical Ways to Display Integer and Decimal Parts Separately

When you need to separate decimals in Excel, this guide covers everything from the fastest methods to compatibility with older versions and regional settings (decimal separator . or ,). It includes formulas for splitting integer and decimal parts as numbers, extracting only the decimal portion as text and fixing it to 2 or N digits, automatically expanding Excel Tables (structured references), and safely handling blank cells, negative values, and errors.

Quick Fix

① Split Integer and Decimal Parts as Numbers (Microsoft 365/2021 or Later)

Integer part (safe for positive and negative values): =TRUNC(A2,0)
Decimal part (0 ≤ value < 1): =MOD(A2,1)
Decimal part only as text (preserves digits):
=TEXTAFTER(TEXT(A2,"0.################"), ".")
Explanation: Converts the number to text to preserve digits, then extracts only the portion after the decimal separator.

② Fixed Decimal Digits (for example, two digits in the form “45”)

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

This converts the decimal part to two digits by multiplying it by 100, pads it with zeros, and returns text. For three digits, expand it as *1000,"000".

③ Alternative Formula for Older Versions (2019 and Earlier)

Decimal part as text (not locale-aware):
=RIGHT(TEXT(A2,"0.############"), LEN(TEXT(A2,"0.############"))-FIND(".",TEXT(A2,"0.############")))
Locale-compatible version (., ,) with automatic detection:
=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&"############"))))

Why Does This Happen? (Concepts and Principles)

INT vs TRUNC vs MOD

TRUNC(n,0) truncates the decimal part (toward zero). TRUNC(-1.8,0)=-1
INT(n) rounds down (toward −∞). INT(-1.8)=-2. This differs from TRUNC for negative values.
MOD(n,1) returns the decimal part in the range of 0 to <1 (the result is 0 to 1 even for negative values). MOD(-1.2,1)=0.8

Regional Separators (Decimal . vs ,) Compatibility

Excel uses different decimal separators depending on Windows/Office regional settings. Hard-coding “.” when extracting the decimal part with text functions can break formulas in European-style (,) environments. It is safer to automatically detect the decimal separator as follows:

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

This detects the current workbook’s decimal separator and stores it in d, then splits based on that character.

Practical Examples

Sample Data

Item	Original Value (Column A)	Description
Positive value	123.456	Standard case
Negative value	-78.9	Note the difference between INT and TRUNC
Integer	45	No decimal part
Blank		Error prevention needed
Text string	“12.30”	Number stored as text

1) Split Integer and Decimal Parts — Keep Them as Numbers

Integer part (Column B): =IF(A2="","",TRUNC(A2,0))
Decimal part (Column C): =IF(A2="","",MOD(A2,1))

Adjust displayed decimal places in cell formatting (Ctrl+1 → Number) using a custom format such as 0.00.

2) Extract Only the Decimal Part as Text (for Preserving Digits or Analysis)

Extracting the decimal portion as text preserves the original digits, such as “0.3000,” making it useful for comparisons, labels, and coding.

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

An integer (45) returns an empty string because there is no text after the decimal separator. If needed, provide a default value such as IF(= "", "00").

3) Fixed 2-, 3-, or N-Digit Decimal Parts (Pad Leading Zeros)

Two digits (“05”, “30”):

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

General formula for N digits: =TEXT(ROUND(MOD(A2,1),N)*10^N, REPT("0",N))

4) Split in One Step with 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)

Example result: 123 | 456. Because this is an array, to spill the results into adjacent cells, enter TEXTSPLIT(...) itself, then reference the first and second spilled cells separately.

5) Automatically Expand with an Excel Table (Structured References)

Convert the data to a table (shortcut: Ctrl+T). If the column name is [Value]:

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

The range expands automatically as rows are added.

6) Report Safeguards (IFERROR/Blank Cells and Text)

With validation: =IFERROR(IF(A2="","",TEXTAFTER(TEXT(A2,"0.################"),".")),"")
Force conversion of text numbers: Insert =VALUE(A2) in the formula to convert text numbers such as “12.30” to numbers before processing.

Alternative Methods, Notes, and Checklist

For negative values, TRUNC and INT differ. Use TRUNC for truncation and INT for rounding down.
Extract as text for labels or codes; split as numbers for calculations.
If you need a fixed number of digits for reporting consistency, use the TEXT( ... , REPT("0",N)) pattern.
If regional (., ,) issues are possible, safely detect the decimal separator with LET(d, MID(TEXT(1.1,"0.0"),2,1), ...).
Formatting alone cannot extract the decimal part into another cell. Use functions to split values across cells.

Troubleshooting

Issue	Cause	Solution
Integer part differs from expectations for negative values	Using `INT` (rounding down)	Use `TRUNC` to truncate, or choose based on your policy
Formula fails on a European-style (,) PC	The decimal separator is hard-coded as “.”	Use the `LET(d, MID(TEXT(1.1,"0.0"),2,1), ... d ...)` pattern for automatic detection
0 appears instead of a blank for integer values	No default return value is specified	Specify a default string, such as `IF(= "", "00")`
Text numbers such as “12.30” are not recognized	They are not in number format	Convert them to numbers with `VALUE` before processing
Digits are inconsistent	A fixed-digit format is not used	Use `TEXT(...,REPT("0",N))` for zero-padding

Conclusion: Recommended Related Articles

Additional internal links: How to Reduce Excel File Size, Worksheet Shortcuts

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

Excel Decimal Separation and Display Guide: Split Integer and Decimal Parts, Fixed Digits, Locale Compatibility, and TEXTSPLIT/TEXTAFTER