Auto format

Location on ribbon: Tab DVD Addin → Group Text and Number → Menu Text ops

Command ID: btnFormatAuto

What it does

The command resets the selection's number format to General, then reads each cell: if the content parses as a date it is rewritten as a real date value, if it parses as a number it is rewritten as a real number, otherwise the text is left alone.

This is the quick fix for "numbers that sit on the left" — data pasted from PDFs, web pages, estimating software or CSV files, where SUM returns 0 and the date column refuses to sort.

How to use

1. Select the range to normalise — prefer one number column or one date column at a time rather than the whole table.
2. Go to tab DVD Addin → group Text and Number → menu Text ops → Auto format.
3. It runs immediately, with no dialog. The status bar reports how many cells were converted.
4. The range is left on the General format, so apply your own display format afterwards (for example dd/mm/yyyy for dates, #,##0.00 for quantities).

Example

A quantity sheet received from a subcontractor where both the quantity column and the acceptance-date column arrived as text.

Before (text, left-aligned)	After (real value)
125.5	125.5 — adds up with SUM
1200	1200 — adds up with SUM
15/03/2026	15/03/2026 — date maths and month filters work

Apply #,##0.00 to the quantity column and dd/mm/yyyy to the date column and the sheet is ready to feed the payment summary.

Notes

• The command clears the existing number format of the whole range (sets it to General), so date patterns, thousand separators and decimal places must be reapplied afterwards.
• Recognition follows the machine's Regional Settings. On a vi-VN machine . is the thousands separator and , the decimal mark, so a string like 1.5 may be read as 15. Test a few rows before running a whole column.
• A string like 01/02/2026 is read using the regional day/month order, so US-formatted source data can end up with day and month swapped.
• Cells that parse as neither number nor date stay as text.
• Ctrl+Z cannot undo this.

Related

• Remove extra spaces — clean whitespace first, since padded strings often fail to parse.
• Smart Data Cleaning… — normalise dates to one consistent pattern before converting types.
• Bulk find / replace… — strip stray characters (quotes, unit labels) out of a number column.
• Add text — put the unit label back on a converted column when it is needed for printing.