Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to remove digits after decimal in Excel?

Sometimes you need to remove all digits after decimal to make numbers to whole numbers. You may think the Decrease Decimal feature can deal with it. Actually, the Decrease Decimal feature just changes a number’s display value, for example, show 2.5 as 3, but the actual value still is 2.5 showing in the formula bar. Therefore, how to remove digits after decimal in Excel? There are several methods for you.

Remove digits after decimal with INT function or TRUNC function

Remove digits after decimal without formula by Round utility

Batch add decimal point to existing numbers in a column/list without formula

As you know, decimal point can be automatically added with the specified decimal places when you typing numbers by Excel options > Advanced > Automatically insert a decimal point. However, this option will affect the whole worksheets. What’s about only automatically inserting decimal points to existing numbers in a specified range in Excel? Try Kutools for Excel’s Operation Tools utility! Full Feature Free Trial 30-day!
ad operation tools add decimal points

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Remove digits after decimal with INT function or TRUNC function

Supposing we have a purchase table as below screen shot shown, and now we need to remove digits after decimal for values in Amount Column. Excel functions can help you to remove digits after decimal easily. You can do as follows:

INT function: In a blank cell besides the value you will remove digits after decimal, enter the formula =INT(E2), and the drag the Fill Handle to the range you need.

Notes:
(1) In the formula =INT(E2), E2 is the cell that you will remove digits after decimal.
(2) INT function can also remain certain places of digits after decimal. For example, to remove all digits except the first one after decimal, you can apply the formula =INT(E2*10)/10.

TRUNC function: Besides the value you will remove digits after decimal, enter the formula =TRUNC(E2,0) into a blank cell, and then drag the Fill Handle to the range you need.

Notes:
(1) In the formula =TRUNC(E2,0), E2 is the cell you will remove digits after decimal, 0 means removing all digits after decimal;
(2) To remove all digits but first one after decimal, you can apply the formula =TRUNC(E2,1);

Tip: Remove formulas but keep formula results: (1)select  all formula cells; (2) copy these formula cells with pressing the Ctrl + C keys at the same time; (3) right click the first cell of destination range, and select Values below Paste Options in the context menu.
By the way, If you have Kutools for Excel installed, you can apply its To Actual feature to quickly convert all formulas to their formula results with only one click. See screenshots:
ad to actual 5


Remove digits after decimal without formula by Round utility

All functions don’t modify original values in Excel, but sometimes you may just need to change original values. To remove digits after decimal from original values directly, you can apply the Round utility of Kutools for Excel.

1. Select the cells that you will remove digits after decimal, and then click the Kutools > Round.
doc remove digits after decimal 4

2. In the opening Round without Formula dialog box, type 0 in the Decimal place box, check the Rounding down option, and click the Ok button.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: remove digits after decimal without formula in Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    samuel · 8 days ago
    Many thanks; it has yielded 2 hrs back to me, let me take a deep sleep
  • To post as a guest, your comment is unpublished.
    parveen · 10 months ago
    helps a lot, thanks
  • To post as a guest, your comment is unpublished.
    Fazal · 11 months ago
    awesome very informative
  • To post as a guest, your comment is unpublished.
    NAVYA · 1 years ago
    NICE INFORMATION
  • To post as a guest, your comment is unpublished.
    Suhas B R · 1 years ago
    Hi.. I would like to Round the digits to 2 or 3 decimals, but when i use ROUND command it round up the number to the 2 or 3 decimals if the number contains more decimal places. But if the number is whole number for example , it displays as 1. Is there any command to make it as 1.00
    • To post as a guest, your comment is unpublished.
      kellytte · 11 months ago
      Hi Suhas,
      After apply the ROUND function, keep the formula cells selected, and click the Increase Decimal button and Decrease Decimal button under Home tab to unify their decimal places.
  • To post as a guest, your comment is unpublished.
    vaji · 1 years ago
    Thanks very helpfull
  • To post as a guest, your comment is unpublished.
    raj · 2 years ago
    Thanks it's very helpful.... :-)
  • To post as a guest, your comment is unpublished.
    Shailendra Vatsa · 2 years ago
    Thanks it's very helpful.... :-)