Excel · Editor — text & fill

Middle of the Excel Editor group: clean and reshape text, normalise dates, and fill blanks or sequences.

1. Map (this chapter)

Formulas & numbers: Editor — formulas. Cleanup: Editor — cleanup.

2. Text

Character- and whitespace-level cleanup (separate from Batch clean), grouped by purpose in one dialog.

1. Select the cells.
2. Click Text.
3. Pick an operation from the dropdown, grouped as: - Find — Find & replace (plain text or a regular expression) - Clean — Trim leading/trailing spaces, Collapse multiple spaces, Remove all spaces, Remove line breaks, Remove invisible characters, Remove HTML tags - Numbers — Convert text to number (locale-aware: works out whether , or . is the decimal separator when both appear, and strips a leading currency symbol or a trailing %), Remove currency symbols - Edit — Prepend text, Append text, Insert text at position, Remove first/last N characters, Truncate to N characters, Reverse text - Remove — Remove specific characters (presets: punctuation, digits, letters, whitespace, or a custom set you type), Remove by regex pattern - Sort — Sort words in cell, by a delimiter you choose, ascending or descending
4. Fill in the fields the chosen operation needs — the description under the dropdown explains what it does.
5. Check Write results to the column to the right (non-destructive) to keep the original values and place results next to them instead of overwriting.
6. Click Apply.

Regex-based operations (Find & replace with regex, Remove by regex pattern) time out after a couple of seconds on a runaway pattern instead of freezing Excel.

3. Extract pattern

Pulls structured pieces out of free-text cells; results are written to the column immediately to the right of the selection.

1. Select a single column of cells.
2. Click Extract pattern.
3. Choose what to extract: - Numbers — the first (or all) numbers in each cell, including parenthesised negatives, e.g. (1,234.56) → -1234.56 - Email addresses - URLs / web links — anything starting with http:// or https:// - Phone numbers — digit sequences that look like phone numbers - ID numbers — long digit sequences (6–20 digits) that look like IDs or reference numbers - Currency codes — 3-letter ISO 4217 codes (USD, GBP, EUR…) - Dates inside text — dd/mm/yyyy, yyyy-mm-dd, 12 Mar 2024, etc. - Custom regex — a pattern you type in
4. If a cell can contain more than one match, choose what to do with the extras: - Take the first match only - Join all matches into one cell (comma-separated) - Expand to multiple columns — writes each match on its own row, next to the source cell’s address
5. Click Extract.

4. Case / type

Five one-click case conversions in a single dialog; each button applies immediately and closes the dialog.

1. Select the cells.
2. Click Case / type.
3. Click one of: - UPPER CASE — hello world → HELLO WORLD - lower case — Hello World → hello world - Title Case — hello world → Hello World - Sentence case — hello world → Hello world - iNVERT cASE — Hello → hELLO

Only text cells are changed; blanks and numbers are left alone. Because the result is written as a plain value, applying a conversion to a formula cell that returns text replaces the formula with static text.

5. Date tools

Parse and normalise mixed date text (40+ patterns, fuzzy 3-group dates, 2-digit years, Julian YYDDD, day-first vs month-first preference) and apply a uniform format across the selection.

1. Select the cells.
2. Click Date tools.
3. Choose auto-recognise or apply format; set ambiguous-order preference if needed; Apply.

6. Smart fill

Fills blank cells in the selection using a neighbouring value or a custom rule. Only genuinely blank cells (no value, no formula) are touched — existing formulas are left alone.

1. Select the range with blanks to fill.
2. Click Smart fill.
3. Choose a direction (which neighbour to pull from): - Down — fill blanks from the value above - Up — fill blanks from the value below - Right — fill blanks from the value to the left - Left — fill blanks from the value to the right
4. Choose a fill source: - Nearest non-empty value — copies the literal value - Nearest non-empty as formula — writes a live reference to the source cell (e.g. =A4) - Fixed value — writes the same value into every blank - Custom formula — write the formula as it would appear in the first blank cell; Navifia pastes it there, then copies it to the rest so Excel auto-adjusts relative references
5. Click Apply.

Typical: section headers in column A with blanks below → Smart fill down with nearest value → flat table for pivots.

7. Fill sequence

Numbers a single column with a sequence, treating each merged group of cells as one entry.

1. Select a single column (merged cells count as one row each).
2. Click Fill sequence.
3. Set: - Start number and Step — e.g. Start 1, Step 1 → 1, 2, 3… - Prefix (optional) — e.g. INV- → INV-1, INV-2… - Zero-pad length (0 = none) — e.g. 4 → 0001, 0002… - Write values / write formulas — checked writes plain numbers; unchecked writes a ROW()-based formula so inserting or deleting rows keeps the sequence correct
4. Click Apply.

Each merged group gets one number; the rest of that merge’s rows are skipped when counting the next value.

8. Suggested workflow

1. Text / Date tools on messy import columns.
2. Extract pattern for IDs or emails buried in free text.
3. Smart fill down on section headers before a pivot.

Troubleshooting

• Convert text to number left some cells — mixed separators or non-numeric junk; clean with Text first, or Extract pattern then convert.
• Smart fill overwrote formulas — choose formula-reference mode only when you want live links to the source cell.

← Editor — formulas · Next: Editor — cleanup →