Excel Power Query Text Clean: How to Fix Messy Data

Errors & fixes · Updated November 22, 2026

Yes, Power Query includes a full set of text-cleaning tools—Trim, Clean, Upper/Lower/Capitalize Each Word, Replace Values, and Split Column—that let you standardize messy text data without writing a single formula. You access them via the Transform tab in the Power Query Editor, and every step is recorded so you can refresh the data later with one click.

What Counts as “Text Cleaning” in Power Query?

Text cleaning means turning inconsistent, messy text into a consistent, usable format. Common tasks include:

  • Removing leading, trailing, or extra spaces
  • Deleting non-printable characters (line breaks, tabs, special control characters)
  • Changing case (uppercase, lowercase, proper case)
  • Replacing typos or variations (e.g., “N/A”, “n/a”, “NA” → “Not Available”)
  • Splitting full names into first and last, or addresses into parts
  • Extracting numbers or text from mixed strings
  • Trimming and cleaning text imported from PDFs, web pages, or legacy systems

Power Query is built for this kind of work because it’s repeatable—you set up the cleaning steps once, and they apply every time you refresh the query.

How to Clean Text in Power Query: Step-by-Step

Here’s the core workflow for cleaning a text column in Power Query (Microsoft 365, Windows):

  1. Select your data in Excel, go to Data > From Table/Range (or use Data > Get Data to import from an external source).
  2. In the Power Query Editor, select the column(s) you want to clean.
  3. On the Transform tab, use the tools you need. For example:
    • Format > Trim – removes leading and trailing spaces.
    • Format > Clean – removes non-printable characters (like line breaks).
    • Format > UPPERCASE, lowercase, or Capitalize Each Word.
    • Replace Values – find and replace specific text (e.g., replace “St.” with “Street”).
    • Split Column – split by delimiter, number of characters, or positions.
  4. Click Close & Load to put the cleaned data back into Excel.

On a Mac, the steps are identical except the ribbon labels are slightly different (e.g., Data > From Table/Range is still there). Excel for the web does not support Power Query; you’ll need the desktop app.

Trim vs. Clean: What’s the Difference?

Many people confuse these two. Trim removes extra spaces at the beginning and end of a text string, but it also collapses multiple spaces inside the text to a single space. For example, " John Smith " becomes "John Smith".

Clean removes non-printable characters—those invisible control characters like line breaks (CHAR(10)), carriage returns (CHAR(13)), and tabs (CHAR(9)). It does not remove spaces. So if your data has a line break inside a cell, Clean will remove it, but Trim won’t.

In most cleaning workflows you’ll want to apply both: first Clean to remove weird characters, then Trim to tidy up spaces.

How to Handle Inconsistent Capitalization

Capitalization issues are common when data comes from different people or systems. Power Query offers four formatting options under Transform > Format:

  • UPPERCASE – makes everything uppercase (e.g., “john smith” → “JOHN SMITH”)
  • lowercase – makes everything lowercase (e.g., “JOHN SMITH” → “john smith”)
  • Capitalize Each Word – makes the first letter of each word uppercase (e.g., “john smith” → “John Smith”) – note it also lowercases the rest of the word, so “JOHN SMITH” becomes “John Smith”.
  • Trim and Clean are also in that menu, though they’re not case-related.

Be careful with Capitalize Each Word: it will capitalize every word, so “O’Brien” becomes “O’brien” and “McDonald” becomes “Mcdonald”. If you need to preserve proper names like that, you may need a custom column with a more complex formula.

Using Replace Values and Split Column for Real-World Text Problems

Replace Values is a straightforward find-and-replace, but it’s powerful for standardizing data. For example, if your column has “N/A”, “n/a”, “NA”, and “Not Available”, you can run Replace Values multiple times to turn them all into “Not Available”. You can also use it to remove unwanted characters, like replacing a hyphen with a space, or replacing a comma with nothing.

Split Column is essential when you have combined data. For instance, a column with “Last, First” can be split by comma to get two columns. Or a column with “Product-123” can be split by delimiter “-” to separate the product name and code. You can also split by number of characters (e.g., extract the first 3 characters as a code).

Both tools are on the Transform tab. After splitting, you can rename the new columns, change data types, and continue cleaning.

Common Problems and How to Solve Them

Here are typical issues users run into when cleaning text in Power Query:

  • Problem: Trim doesn’t remove non-breaking spaces (character code 160) imported from web pages.
    Solution: Use Replace Values to replace that specific character. You can copy the character from the cell, or use the formula Text.Replace with Char.FromNumber(160) in a custom column.
  • Problem: Clean doesn’t remove all invisible characters, like some Unicode control characters.
    Solution: Use a custom column with the Text.Remove function to strip a range of character codes, or use the “Replace Values” with the actual character.
  • Problem: Capitalize Each Word messes up names like “McDonald”.
    Solution: After using Capitalize Each Word, manually fix exceptions with Replace Values, or use a custom column with a more nuanced formula.
  • Problem: The data has leading zeros that get removed when Excel treats the column as numeric.
    Solution: In Power Query, change the column type to Text before loading, or use the “Add Column > From Text > Prefix” to add a leading apostrophe (though that’s not ideal). Better: keep the column as text in the query.

Power Query vs. Excel Formulas for Text Cleaning

You can clean text with Excel formulas like =TRIM(A1), =CLEAN(A1), =UPPER(A1), or =SUBSTITUTE(A1,"old","new"). But formulas have limitations:

  • You need to create a new column for each transformation, and the original data remains unchanged.
  • If your data changes, you have to drag formulas down or use dynamic arrays.
  • Complex cleaning (e.g., splitting into multiple columns) requires multiple formulas and can get messy.
  • Power Query records each step, so you can audit and modify the cleaning logic easily.

Power Query is the better choice when you have large datasets or need to repeat the cleaning on a regular basis (e.g., monthly reports). For one-off small tasks, formulas might be quicker.

Frequently Asked Questions

How do I remove all spaces from a text column in Power Query?

Use Replace Values to replace a space (press spacebar in the “Value to find” box) with nothing (leave “Replace with” empty). That will remove every space, not just leading/trailing. If you only want to remove extra spaces, use Trim.

Can I use Power Query to clean text in Excel for the web?

No, Power Query is not available in Excel for the web. You need the desktop version of Excel (Windows or Mac). If you only have the web version, you can use formulas like TRIM and SUBSTITUTE, but you won’t get the repeatable query workflow.

Why does my Trim not remove some spaces?

Trim only removes regular spaces (character 32). Non-breaking spaces (character 160) are not removed. To remove those, use Replace Values with the non-breaking space character (copy it from a cell) or use a custom column with Text.Replace.

How do I capitalize only the first letter of each sentence, not each word?

Power Query doesn’t have a built-in “Sentence Case” option. You can use a custom column with a formula like Text.Upper(Text.Start([Text],1)) & Text.Lower(Text.Range([Text],1)) to capitalize the first letter and lowercase the rest, but that only works for the entire string, not per sentence. For full sentence case, you’d need a more complex M function.

Can I undo Power Query cleaning steps?

Yes, in the Power Query Editor you can delete steps from the “Applied Steps” pane on the right. Each step is recorded, so you can go back to any point and remove or modify a step. However, once you load the data into Excel, the query is separate—you can’t undo the load with Ctrl+Z, but you can edit the query and refresh.

Is there a way to clean text in multiple columns at once?

Yes, select multiple columns (hold Ctrl and click column headers) and apply the transformation (e.g., Trim, Clean, Uppercase) to all selected columns at once. For Replace Values, you can also select multiple columns, but the replacement will apply to all selected columns.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.