Excel Formula Split Dollars and Cents

To split dollars and cents in Excel, use =INT(A1) for the dollar part and =MOD(A1,1) for the cents part, or =ROUND(A1,0) and =A1-ROUND(A1,0) if you need to round cents to a whole number. This article explains the formulas, how to handle negative amounts, and common pitfalls.

Formula

To separate a decimal number into its integer (dollar) and fractional (cent) parts, use two formulas:

• Dollar part: =INT(A1) – returns the integer part of the number, e.g., INT(123.45) = 123.
• Cent part: =MOD(A1,1) – returns the remainder after dividing by 1, which is the decimal fraction, e.g., MOD(123.45,1) = 0.45.

If your cents are stored as a whole number (like 45 for $0.45), multiply the fraction by 100: =MOD(A1,1)*100.

Worked Example

Using the default value of 123.45:

1. Dollars: =INT(123.45) → 123
2. Cents: =MOD(123.45,1) → 0.45
3. Cents as whole number: =MOD(123.45,1)*100 → 45

So you get $123 and 45 cents.

Why Use INT and MOD Instead of Other Functions?

There are several ways to split dollars and cents, but INT and MOD are the most direct. The TRUNC function also gives the integer part but handles negative numbers differently. ROUND rounds to the nearest whole number, which may not be what you want if you need to preserve the exact cents. TEXT can format but returns text, not numbers. INT always rounds down (toward zero for positive numbers, away from zero for negatives), which is exactly what you need for currency.

Handling Negative Amounts

For negative numbers, INT rounds down (more negative), so INT(-123.45) returns -124, not -123. If you want the dollar part to be -123 (the amount before the decimal), use TRUNC instead: =TRUNC(-123.45) gives -123. For cents, MOD(-123.45,1) returns 0.55, which is the positive remainder. To get -0.45, use =-123.45 - TRUNC(-123.45). Decide based on how you want to display negative amounts.

When to Use This in Real Work

You might need to split dollars and cents for accounting reports, invoice breakdowns, or when importing data that requires separate columns. For example, if you have a list of payments and need to sum only the dollar parts, or if you need to write a check where the cents are written as a fraction. The formulas are simple and can be applied to a whole column with a fill handle.

Common Mistakes and How to Avoid Them

• Using ROUND instead of INT – ROUND(123.45,0) gives 123, but ROUND(123.50,0) gives 124, which changes the dollar amount. Use INT or TRUNC to keep the original dollar part.
• Forgetting to multiply by 100 – If you want cents as a whole number (45), don’t forget to multiply the fraction by 100.
• Not handling negative numbers – As noted, INT and MOD behave differently for negatives. Test your data.
• Formatting cells as currency – If you format the cents cell as currency, it will show $0.45, not 45. Use number format with 0 decimal places if you want whole cents.

Alternatives and Variations

If you need to split a number into dollars and cents as text (for example, for a check-printing template), you can use =TEXT(A1,"0") for dollars and =TEXT(A1,".00") for cents. But these return text, so you can’t use them in further calculations. Another approach is to use =QUOTIENT(A1,1) for dollars, but QUOTIENT is not available in all Excel versions. The INT and MOD combination is the most widely compatible and easy to understand.

Frequently Asked Questions

How do I get cents as a whole number like 45 instead of 0.45?

Multiply the result of MOD(A1,1) by 100: =MOD(A1,1)*100. Format the cell as a number with 0 decimal places to display 45.

What if my amount is in a cell as text like “$123.45”?

First convert the text to a number using =VALUE(A1) or use =--A1, then apply the formulas. If the text includes a dollar sign, Excel might already recognize it as a number if it’s formatted as currency.

Can I split dollars and cents without a formula?

Yes, you can use Excel’s Text to Columns feature. Select the column, go to Data > Text to Columns, choose Delimited, and use the decimal point as a delimiter. This will split the number into two columns, but it changes the original data.

Does this work in Google Sheets?

Yes, the same formulas work in Google Sheets. INT and MOD are supported identically.

Why does MOD(-123.45,1) give 0.55?

MOD returns the remainder with the same sign as the divisor (1), so it’s always positive. If you need the negative cents, use =-123.45 - TRUNC(-123.45).

How do I split a number into dollars and cents for a whole column?

Enter the formula in the first cell, then drag the fill handle down. Excel will adjust the cell references automatically.

Related

• Litre vs 15 Litre Geyser: Which Capacity Is Better?
• GTA Online Money Fronts: Car Wash Mission Guide
• Natural Rubber Tires: Safety & When to Replace

in amount | Amount | $ | 123.45 | 0
out dollars | Dollars | $ | INT(amount) | 2
out cents | Cents | $ | MOD(amount, 1) | 2