Faragiri

Learn Cleaning data Excel

An NGO field export in Bogra: the tests that tell you what a column really holds, the order to clean in, and why a date that looks right can still be a word.

7Sections
4Min read
4Depth

Rafiq imports 5,400 rows of field survey data in Bogra every month. The dates arrive as 03/04/2026, they look like dates, and 2,700 of them are text that no sort and no filter will treat as a date.

The first job on any imported sheet is not cleaning it. It is finding out what is actually in it, because the visible form of a value says almost nothing about its type.

Three tests, before touching anything

=ISNUMBER(A2) says whether a value is a number. Dates and times are numbers in Excel, so a real date returns TRUE and a text date returns FALSE. Dragged down a column, it counts the problem in one move.

Before touching anything

Three tests that diagnose column corruption before writing formulas

A value's visible appearance says almost nothing about its underlying storage type in Excel.

Diagnostic triage
  • =ISNUMBER(A2)Tests whether a value is stored as a number; real dates return TRUE while text dates return FALSE
  • =LEN(A2)Compares visible character count against reported length to find trailing and invisible spaces
  • Default cell alignmentNumbers and dates align right by default; left-aligned values expose text storage without formulas

Raw imported sheet

Running these three checks down a sample column pinpoints whether the cleanup requires trimming, type conversion, or date parsing before any data is altered.

=LEN(A2) against the visible length finds trailing spaces and invisible characters: a name that looks 6 characters long and reports 7 has something on the end.

And alignment, which costs nothing: numbers and dates sit right by default, text sits left. A column of right-aligned values with a few strays on the left is showing you its own fault before any formula is written.

Clean in this order

Order matters, because each step can undo the previous one.

5,400 rows, once a month

Four cleaning steps in the sequence that prevents each from undoing the last

Deduplication must run last because two rows differing only by a trailing space look distinct until trimming removes the space.

  1. step 1Remove unwanted charactersRun =TRIM(CLEAN(A2)) in a new column to strip non-printing characters and stray spaces
  2. step 2Fix storage typesConvert text numbers with =VALUE() and repair text dates using Text to Columns
  3. step 3Split or combine columnsUse TEXTSPLIT in modern Excel or Text to Columns for delimited one-off imports
  4. step 4Remove duplicate rowsDeduplicate only after spaces are gone so false uniqueness disappears

For a monthly recurring import, these four steps belong in a recorded query where they execute automatically in strict sequence without manual intervention.

First, remove what should not be there: TRIM for spaces at the ends and doubles in the middle, CLEAN for the non-printing characters an export leaves behind. Both at once as =TRIM(CLEAN(A2)).

Second, fix the types. Text numbers become numbers with =VALUE(); text dates need DATEVALUE or, more reliably, Text to Columns.

Third, split or join. TEXTSPLIT, in Microsoft 365 since 2022, splits on a delimiter into separate columns; before that it was Text to Columns from the Data ribbon, which still works and is still the fastest route for a one-off.

Fourth, deduplicate, which has to be last because two rows differing only by a trailing space are not duplicates until step one has run.

The date problem, specifically

A date stored as text is the commonest import fault and the hardest to see. Sorting it puts 03/04 before 12/03 because it is sorting strings, and a pivot cannot group it by month.

03/04/2026 ambiguity

Two conflicting date interpretations hidden inside identical text

Sorting text dates puts 03/04 before 12/03 because strings sort alphabetically rather than chronologically.

DMY date formatCommon outside North America
MDY date formatStandard US software export
Parsed calendar dayThird of AprilFourth of March
Wizard setting requiredSelect DMY on step 3 of Text to ColumnsSelect MDY on step 3 of Text to Columns
Impact of wrong choicePreserves intended day and month orderSilently misallocates dates by an entire month

Because the text itself cannot reveal creator intent, the date order must be verified against the source system rather than guessed from the spreadsheet.

The reliable repair is Data, then Text to Columns, then Next, then Next, then choose Date and tell it the order the text is in: DMY for 03/04/2026 meaning the third of April, MDY if it means the fourth of March. That last screen is the whole point of the exercise and it is on the third page of a wizard most people cancel on the first.

The ambiguity is real and unresolvable from the data: 03/04/2026 is two different days depending on where the export came from. It has to be established from the source rather than guessed, and getting it wrong silently shifts a third of a year's records.

Flash Fill, for the pattern you can demonstrate

Type the result you want beside the first row, then Ctrl+E. Excel infers the pattern and fills the column.

Ctrl+E pattern inference

Fast one-off extraction versus recurring batch reliability

Flash Fill infers transformation rules from an example row, but it cannot update dynamically when new rows arrive.

  • One-off tidyingWhere Flash Fill excels
    • Splits full names into components in seconds
    • Extracts codes from the middle of references
    • Re-formats phone numbers without nested formulas
  • Monthly pipelineWhere Flash Fill fails
    • Does not update when source cells change
    • Fails silently on rows deviating from inferred pattern
    • Cannot be recorded or audited as a repeatable step

Operational choice

Rafiq reserves Flash Fill for manual ad-hoc corrections and relies on structured transformations for the recurring monthly import.

It is remarkable for splitting names, extracting a code from the middle of a reference, or reformatting a phone number, and it is a one-time transformation rather than a formula: it does not update when the source changes, and it will silently produce a wrong result on rows that do not match the pattern it inferred from the ones it saw.

Rafiq uses it for one-off tidying and never for the monthly import, which is the right division.

Two habits that save the whole afternoon

Clean in new columns, not over the original. Keep the raw column until the sheet is finished, because a cleaning step applied twice is not always visible and the original is the only way back.

Data loss prevention

Two protective barriers that keep spreadsheet cleanups reversible

Cleaning in place without preserving the original column risks irrecoverable damage when an intermediate formula fails.

  1. Clean in parallel columnsOverwriting raw inputs irreversiblyUntouched original column remains available as a fallback
  2. Paste values before source deletion#REF! cascade when raw columns are droppedCtrl+Shift+V converts dynamic formulas into permanent values
Preservation safeguards

A cleaning step applied twice is difficult to detect by eye, making an untouched raw column the only dependable recovery path.

And convert formulas to values before deleting the source column, with Ctrl+Shift+V. A column of =TRIM(A2) dies the moment column A is removed, which is the last step of many cleaning jobs and the reason many of them have to be redone.

When the answer is not to clean it in Excel at all

If the same export arrives every month, cleaning it by hand is a monthly cost with no end, and the steps above are exactly what a query records once and replays.

Recurring monthly cost

Transitioning from manual grid edits to recorded query pipelines

Re-applying manual formula steps to the same monthly export creates endless recurring labor.

Manual spreadsheet cleaningRepeating TRIM, split, and deduplication formulas by hand every month
  1. 01Define transformation steps onceCharacter cleaning, column splitting, and date parsing recorded in the query editor
  2. 02Eliminate human errorSteps replay in exact sequence without relying on manual memory or keystrokes
  3. 03Instant monthly refreshNew survey files transform into clean analytical tables in a single click
Recorded Power QueryOne automated pipeline that ingests raw files and replays transformations

When an identical export arrives every month, building a recorded query replaces recurring manual labor with deterministic automation.

What to clean it with, as of 2026

TRIM, CLEAN, VALUE and LEN, in every version, which replaced looking for the fault by eye in 5,400 rows.

Evolution of cleaning tools

Milestones in how spreadsheets handle dirty text and dates

Modern functions and assistants eliminate brittle nested formulas while preserving human verification of source logic.

  1. 1990sText to Columns wizardData ribbon utility that remains the primary tool for specifying DMY date order
  2. 2013Flash Fill pattern inferenceCtrl+E introduced one-click pattern demonstrations for quick text extraction
  3. 2022Dynamic array text functionsTEXTSPLIT, TEXTBEFORE, and TEXTAFTER replaced unreadable nested LEFT and FIND formulas
  4. 2026AI formula draftingModels draft complex parsing formulas quickly but cannot resolve source date ambiguity

AI assistants accelerate formula drafting, yet verifying whether 03/04/2026 represents March or April remains an external domain decision.

Text to Columns, on the Data ribbon since the 1990s and still the most reliable route for a text date, because it is the only one that lets you state the day-month order.

TEXTSPLIT, TEXTBEFORE and TEXTAFTER, in Microsoft 365 since 2022, which replaced nested LEFT, RIGHT and FIND formulas that nobody could read a month later.

Flash Fill, Ctrl+E, since Excel 2013, for a pattern you can demonstrate once.

A model given a description of a messy column will write the formula to clean it, free on every major assistant, and it is fast at that. It cannot tell you whether 03/04/2026 is April or March, and that is a fact about the source system rather than about the data.

Doing all of this once and replaying it every month is repeatable imports and transformations, and the type mismatch these tests find is what causes building one, and why it breaks. The cleaned column is what makes a summary possible at all, which is summarising thousands of rows in seconds, under the most used analysis tool in the world.

Alongside this

Above this