You typed “5 days” next to every task in your project tracker, and now the total row shows zero — SUM ignores any cell with letters in it. This Excel custom number format generator builds the format code that displays units like days, kg or $ per hour beside your numbers while the cells keep their real values, so all the maths underneath keeps working. It takes less than a minute: fill in the boxes, copy the code, and paste it into Excel’s Custom format box.
Add units like days, kg or $ per hour to numbers without turning them into text. Fill in the boxes, copy the format code, and your formulas keep working.
Excel: select the cells, press Ctrl+1, choose Custom, paste the code into Type, click OK.
Google Sheets: Format, Number, Custom number format, paste, Apply.
The singular option uses a condition, so it is meant for values that are not negative.
Why typing units into Excel breaks your formulas
Excel sees anything with letters as text. A cell holding “5 days” stops being the number 5 and becomes a word — SUM and AVERAGE skip it or throw an error, and one typed unit can quietly zero out a whole column of totals.
The usual workaround is a helper column: numbers in one column, units typed in the next. It works, but it doubles your columns, clutters the sheet and still leaves the raw numbers looking bare. There is a neater way built right into Excel.
Custom number formats only change how a number looks, never what it is. The cell still holds 5 but displays “5 days” — formulas keep working, sorting keeps working, and the sheet reads exactly how you want it to.
How to use the Excel Custom Number Format Generator
Type the unit into the “text after” box — days, kg, hours, whatever you need — and add any text you want before the number too. Then choose how many decimals you want, from none up to whatever your data needs.
Tick “Put a space before the unit” for “5 days” instead of “5days”, and add a thousands separator for big figures. The tool builds the format code live, and the preview shows exactly what a sample value will look like.
There is also a singular option: tick “Use a singular word when the value is 1” and type the singular form, like “day”. The generator adds a condition so 1 shows “1 day” while 5 shows “5 days”. Copy the code with the button when you are happy with it.
Applying the code in Excel: Ctrl+1 → Custom → Type
Select the cells you want to format and press Ctrl+1 to open Format Cells. Pick the Custom category, click into the Type box, paste the generated code and press OK — the numbers instantly show their units.
Nothing underneath has changed: click a cell and the formula bar still shows the plain number. That is the whole trick — display and value stay separate, so every formula keeps working.
To reuse the code, paste it into another workbook’s Custom Type box or save it as a cell style. One code formats a whole column in seconds, with no helper columns and no retyping. If the units don’t appear, check the code went into the Type box and not into the cell itself.
Excel vs Google Sheets: what works where
Custom number formats exist in both Excel and Google Sheets, and the basic building blocks — quoted text like “days”, decimal placeholders, thousands separators — work the same in both. In Sheets, go to Format → Number → Custom number format and paste the code there.
The singular option is the one place to be careful. It uses a condition in the format code, and conditions behave slightly differently between Excel and Sheets; the tool notes that the singular form is meant for values that are not negative. For plain units with no singular form, the same code works in both apps.
If you mainly work in Sheets, generate the code without the singular option and test it on one cell first. The preview in the generator shows you the Excel rendering, so you know what to expect before you paste.
Worked example: showing “8.5 days” with one decimal
Say you track task durations and want one decimal place with the word “days”. Enter “days” as the unit, choose 1 decimal, and keep “Put a space before the unit” ticked. The generator produces the code 0.0 “days”.
The preview renders a sample value as “8.5 days”, while the cell still holds the number 8.5. Paste the code via Ctrl+1 → Custom → Type and the whole column follows — a cell holding 12 becomes “12.0 days”, ready for charts and totals alike.
Try the same with “kg” and a thousands separator for weights, or build a rate with “$” before the number and “per hour” after it: with 2 decimals, 45.5 displays as “$45.50 per hour” while staying fully calculable.
Three presets to try: days, kg and $ per hour
For project tracking, use the unit “days” with 1 decimal and a space before the unit: durations read “8.5 days” and still add up correctly at the bottom of the column. This is the most common setup and a good first test of the generator.
For stock or shipping weights, use “kg” with 0 decimals and the thousands separator ticked: 1500 displays as “1,500 kg”. The separator keeps big numbers scannable while the underlying values stay numeric for reorder calculations.
For freelance rates, put “$” before the number and “per hour” after it with 2 decimals: 45.5 becomes “$45.50 per hour”. Invoices look professional, and the rate column can still feed straight into total-cost formulas.
Where custom formats fit your workflow
If you counted days with the Days Per Year Calculator and pasted the totals into a sheet, format the column with the “days” unit so the report reads properly without a helper column. The numbers stay numeric, so you can still average them or chart them.
Already stuck with text like “12 days” in your cells? The Split Numbers and Units tool pulls the numbers back out first — then apply a custom format so it never happens again. And when your formulas reference the newly formatted cells, the Bulk Formula Reference Locker tidies the cell references in bulk.
The habit to build is simple: numbers stay numbers, and formatting does the talking. Once a sheet is set up this way, adding units is a one-click job instead of a data-cleaning exercise.
Excel custom number format with text: what the code does and does not do
The generated code only changes a cell’s display. It cannot change the stored value, round your numbers, or turn text into numbers. If a cell holds 8.567 and your format shows one decimal, the stored value is still 8.567 and formulas use 8.567 — not the rounded display.
Likewise, the format travels with the cell when you copy it, but it does not travel inside a CSV export: CSV carries values only, so your units will not appear in the exported file. Open the CSV in Excel and reapply the format if you need the units back.
Excel custom number format with text FAQs
How do I add units to numbers in Excel without breaking formulas?
Use a custom number format instead of typing the unit into the cell. Generate the code with the tool, then apply it with Ctrl+1 → Custom → Type. The number stays a number, so SUM, AVERAGE and every other formula keep working while the unit shows on screen.
Why does typing “5 days” break my formula?
Because Excel reads “5 days” as text, not as the number 5. Text cells are skipped by SUM and AVERAGE, and other formulas may throw errors — one typed unit can quietly zero out a whole column of totals.
Does the same format code work in Google Sheets?
Mostly yes. The building blocks — quoted text, decimal places and thousands separators — work the same in both. The singular-word option uses a condition that can behave differently in Sheets and is meant for values that are not negative, so test that one on a single cell first.