Formula Errors

How to Format Feet and Inches Rounded to Eighths in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs an Excel formula to convert decimal measurements into feet and inches rounded to the nearest eighth, while suppressing unwanted spaces when the fractional part is zero.

Product: Spreadsheets
Device & OS: not provided
Scenario: Formatting decimal measurements for architectural, engineering, or construction data presentations.
Observed behavior: Standard fraction formatting in Excel sometimes adds an unwanted empty space for very small values or whole numbers when rendering feet and inches.

Before you start

Ensure your decimal measurement is located in a single cell (like B2) and represents the total value in feet before applying the formula.

Solution 1Recommended

Use a Conditional Formula to Format Feet and Inches

Apply a combination of ROUNDDOWN, MOD, IF, and TEXT functions to accurately display feet and inches rounded to the nearest eighth, removing any empty fractional spaces.

This formula breaks down the decimal value into whole feet and fractional inches. It uses conditional logic (IF and MOD) to check if the rounded result is a whole number, thereby suppressing the fraction formatting space when it's not needed.

Select the target cell

Click on the empty cell where you want the formatted feet and inches to appear.

Enter the complex formatting formula

Assuming your original decimal measurement in feet is in cell B2, copy and paste the following formula into the formula bar: =ROUNDDOWN(B2,0)&"' - "&IF(MOD(B2*12,1)=0,B2*12-ROUNDDOWN(B2,0)*12,IF(MOD(ROUND((B2-ROUNDDOWN(B2,0))*12*8,0)/8,1)=0,ROUND((B2-ROUNDDOWN(B2,0))*12*8,0)/8,TEXT(ROUND((B2-ROUNDDOWN(B2,0))*12*8,0)/8,"# ?/?")))&"\""

Apply and fill down

Press Enter to apply the formula. If you have multiple measurements, click the bottom-right corner of the cell and drag the fill handle down to apply it to the rest of your column.

Cell Reference: If your raw data is in a different cell, carefully replace all instances of 'B2' in the formula with your actual target cell reference.

Format Complex Measurements Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical and text formatting formulas, making it easy to calculate and display specialized formats like feet and inches for your construction or design projects without errors.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your decimal measurements.
2. Select the output cell: Click on the cell directly next to your decimal data where you want the feet and inches to show.
3. Paste the formula: Paste the provided formula into the formula bar and press Enter to instantly format your data.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports complex conditional text functions (IF, TEXT, MOD).Lightweight application that processes large data sets quickly.Free to use for everyday spreadsheet and formatting tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard fraction format add an extra space in Excel?

The standard fraction format (like '# ?/?') automatically reserves space for a fraction to keep numbers aligned in a column, even if the value evaluates to a whole number. Using an IF function helps conditionally detect whole numbers and remove this reserved space.

Can I change the rounding precision to sixteenths instead of eighths?

Yes. You can modify the provided formula by changing the multiplier and divisor from 8 to 16 in the ROUND sections of the formula to round to the nearest sixteenth of an inch.

Why are my quotation marks causing an error in the formula?

Ensure you are using straight quotes (") instead of curly or smart quotes (“ ”). Copying and pasting formulas from certain text editors or web pages might convert them to curly quotes, which spreadhseet applications do not recognize as text delimiters.

Excel & Data Problems