Text Utilities

The Text and Number group carries two clusters of cell-content commands: the Italic button and its menu, which format or reorder parts of a single cell around a delimiter, and the Text ops menu, which gathers cleanup, string insertion, data-type conversion and sub/superscript formatting.

This is the everyday toolkit for taking over a bill of quantities, a cost estimate or a staff list produced by someone else: pasted data usually carries stray spaces, wrong data types, units written m2 instead of m², or needs bilingual layout inside one cell.

Commands in this group

Command	What it does
Italic	Splits the cell on a delimiter and italicises the even-numbered chunks (Ctrl+Shift+I)
Text Reverse	Reverses the order of the parts around the delimiter (Ctrl+Shift+R)
Font Change	Applies a different font to the text after the delimiter (Chr(10) or / only)
Remove extra spaces	Runs TRIM per cell: strips both ends, collapses runs of spaces
Add text	Adds a prefix or suffix across the whole selection
Remove accents	Strips Vietnamese diacritics; Đ becomes D
Add accents (AI)	Uses Gemini to restore diacritics on unaccented text
Auto format	Turns "left-aligned numbers" into real numbers and dates
Superscript	Raises a digit sitting right after m/M
Subscript	Lowers digits after a letter, raises charge signs
Bulk find / replace…	Find & replace across sheets and workbooks, with preview and regex
Smart Data Cleaning…	Six families of cleanup rules applied in a single run

Cleaning up a bill of quantities you just received

1. Save the file first. Most commands in this group cannot be undone with Ctrl+Z.
2. Select the description column → Text ops → Remove extra spaces. Do this before any lookup work: stray spaces are the classic reason VLOOKUP and SUMIF fail to match a work code that looks identical on screen.
3. Select the quantity and date columns → Auto format. The range is left on the General format afterwards, so re-apply #,##0.00 for quantities and dd/mm/yyyy for dates.
4. Select the unit column → Superscript so m2 and m3 print as m² and m³. The stored value stays m2, so formulas and pivots are unaffected.
5. To stamp a package code on every row, open Add text, type the string in Text to insert, set Position to Prefix (before existing text), keep Skip empty cells ticked and click Apply. The window does not lock Excel, so you can select the next block and click Apply again.
6. For a bilingual table: select the two-line cells (Alt+Enter) → Italic → the input box asks for the delimiter, keep the default Chr(10) → OK. The English line turns italic while the Vietnamese line stays upright.
7. If the English line also needs its own font, run Font Change: the first input box is the delimiter (Chr(10) or /), the second is the font name (default Times New Roman).

Example

A foundation bill of quantities pasted from a subcontractor's estimating software:

Cell	Before	Command	After
B5	Bê tông lót móng M100	Remove extra spaces	Bê tông lót móng M100
C5	m3 (text)	Superscript	m³ on screen, value still m3
D5	125,5 left-aligned	Auto format	125,5 as a real number, SUM works
B5	Bê tông lót móng M100	Add text, prefix F1 -	F1 - Bê tông lót móng M100
E5	Ván khuôn móng - AF.81110	Text Reverse, delimiter -	AF.81110 - Ván khuôn móng

After these five steps the sheet merges cleanly into the project-wide summary and the lookup against the unit-price table matches again.

Tips and caveats

• Ctrl+Z is effectively unavailable across this whole group. Save the file or duplicate the sheet before running on a large range.
• Remove extra spaces does not handle NBSP (code 160) or zero-width characters that come with text copied from the web — use Smart Data Cleaning… for those.
• Subscript changes the cell content, not just the formatting: every space is stripped and spaces are inserted around + and =. Never run it on cells containing prose.
• Auto format follows the machine's Regional Settings. On a vi-VN machine the string 1.5 can be read as 15, and 01/02/2026 is read day/month. Check the first few rows before running down a whole column.
• Add text skips formula cells and cells on hidden rows, and trims the result at both ends — a string typed with a leading or trailing space loses that space.
• Add accents (AI) calls Gemini once per cell, so a few hundred cells is slow and quota-hungry; the output is a guess and must be proof-read for proper nouns and material codes before the document is signed off.
• Font Change turns on Wrap Text and auto-fits the row height, so the layout can shift; it accepts only the two delimiters Chr(10) and /.

See also

• Bulk Find / Replace — replacement with preview across sheets and workbooks.
• Smart Data Cleaning — the combined cleanup rule set, including duplicate-row removal.
• Smart Case — normalise capitalisation once the spaces are gone.
• Translate — build the bilingual cells that Italic and Font Change then format.