For a safe Excel cleanup, keep the imported data intact, create cleaned columns, and check the results before replacing anything. Use TRIM, CLEAN, and, when needed, SUBSTITUTE to handle common spacing problems; normalize capitalization selectively; and split names or addresses only when the source follows a known format. For recurring imports, Power Query can make those steps repeatable.
Start by preserving the original data
Make a copy of the source columns or work on a separate worksheet before changing imported contact records. You can also convert the range to an Excel table or load it into Power Query. Keeping the source lets you compare the original with the cleaned output and recover values if a transformation changes something it should not.
Inspect a sample of rows before choosing a cleanup method. Look for leading or trailing spaces, repeated spaces, nonbreaking spaces, line breaks, mixed capitalization, punctuation, missing components, and more than one input format. A formula or split rule that works for one pattern may damage another.
In Power Query, be cautious about automatic type changes and changes to source column names or table structure: Microsoft warns that type changes can cause errors or unintended results, and query steps can depend on column and table names. Preserve original columns and verify the query after changes to the source. Microsoft’s Power Query guidance covers these workflow considerations.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchRemove extra spaces and nonprinting characters
Clean ordinary spaces and ASCII control characters
For a basic cleanup, enter this in a new column and fill it down:
=TRIM(CLEAN(A2))
TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to single spaces. Microsoft specifies that it handles the 7-bit ASCII space character, value 32; it does not remove the nonbreaking space, value 160, by itself. CLEAN removes the first 32 nonprinting ASCII characters, values 0 through 31, but it does not remove every possible Unicode control character. See Microsoft’s TRIM function and CLEAN function documentation.
Replace nonbreaking spaces when they are present
If imported text contains character 160, replace it with an ordinary space before trimming:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
This pattern handles the known nonbreaking space alongside the characters handled by TRIM and CLEAN; it is not a universal fix for every Unicode character or formatting issue. Check the cleaned values against the source. Microsoft’s data-cleaning overview describes using SUBSTITUTE for higher-value characters such as 160.
Rank #3
Standardize capitalization without changing identity
Use UPPER or LOWER for fields with a consistent case convention, such as codes or email addresses. PROPER can make ordinary names look more consistent, but it only changes letter case; it cannot determine a person’s or organization’s authoritative spelling.
Review names that include particles, hyphens, apostrophes, internal capitals, or acronyms before applying PROPER broadly. For example, a formatting function may change an internal capital or turn an acronym into title case. Keep exceptions or apply the transformation only to records where the desired convention is clear. Microsoft’s data-cleaning guidance presents these case functions as text transformations, not name validation.
Split names and addresses only when the format is known
Names with a consistent delimiter
If every value follows a format such as Last, First, split on the comma. In Power Query, select the relevant column, then choose Split Column and split by delimiter. Choose whether to split at the leftmost, rightmost, or each occurrence, and set the number of resulting columns to suit the source. Microsoft’s Power Query instructions for splitting text columns describe these options.
For a stable pattern, formulas such as LEFT, MID, RIGHT, SEARCH, and LEN can extract parts of a value. But a space is not a reliable universal boundary: middle names shift the surname’s position, and multiword surnames or titles add further variation. Microsoft’s guidance on splitting text with functions notes middle names as a complication.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Addresses with a consistent schema
Split a street, unit, locality, region, or postal code only when the source schema or delimiter makes the boundaries unambiguous. Address conventions vary by country and by source, so splitting every address at commas or spaces can put parts in the wrong columns. The available Power Query split operations are useful when the input structure is consistent; they do not establish a universal address-parsing rule.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Find duplicate records without merging different people
Power Query removes duplicates based on the columns selected for comparison. For contact records, choose comparison fields that match the question you are trying to answer, then inspect the candidate rows before deleting them. Comparing names alone can treat two distinct people as duplicates; variations in address text can also conceal repeated records. Microsoft’s duplicate-row guidance explains how selected columns determine the comparison.
For likely misspellings or other near matches, Power Query fuzzy matching can surface similar text values. Microsoft describes the method as using Jaccard similarity and documents a default similarity threshold of 0.80, which can be configured. Treat matches as candidates for human review, not proof that two records identify the same person or address. See Microsoft’s fuzzy matching guidance.
Choose formulas or Power Query based on how often you clean data
| Need | Worksheet formulas | Power Query |
|---|---|---|
| One-time cleanup | Useful when you want to inspect the result row by row in the worksheet. | Can still be used, but may be more setup than a small one-off task needs. |
| Recurring imports | Formula columns can be reused, but you must ensure they cover new rows and match the latest source layout. | Query steps provide a repeatable transformation sequence that can be refreshed. |
| Consistent delimiters | Text functions can extract parts when the pattern is stable. | Split and merge operations let you configure delimiter behavior and resulting columns. |
| Near-duplicate review | Exact comparisons can help identify identical values. | Fuzzy matching can surface similar text, but the results require review. |
Power Query also supports removing duplicates and merging queries using one or more matching columns. After refreshing, check the output, especially if the source column names, data types, or layout may have changed. Microsoft’s documentation on Power Query transformations, duplicate handling, and query behavior documents these capabilities.
Quick Recap
Check the cleaned output before using it
- Compare cleaned values with the preserved source and review rows with unfamiliar punctuation, capitalization, or missing components.
- Confirm that each split column contains the intended component for every input pattern in the dataset.
- Check duplicate candidates in context before deleting records, especially when matching on names or approximate text.
- For recurring imports, refresh the query and verify that changed source columns or types have not altered the result.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
