Power Query Problems

How to Clean Mixed Names and Phone Numbers using Power Query

Tauseeq Magsi Sep 25, 2026 872 views

Question details

The user needs to extract names and phone numbers from cells containing inconsistent formats, labels, and varying separators to prepare the data for Outlook contact imports or CSV export.

Product: Power Query
Device & OS: not provided
Scenario: Preparing a clean, standardized contact list for CSV export or Outlook import from a highly disorganized dataset.
Observed behavior: Standard cleaning methods, Find and Replace, and delimiter-based Split Columns fail to produce correct separation due to the inconsistent structure of the source text.

Before you start

Before attempting to transform the data, clearly map out 3 to 5 sample input cells alongside their exact desired output format to determine the necessary extraction rules.

Solution 1Recommended

Define Transformation Rules and Extract via Column From Examples

Since the data structure varies, you must establish target formats and use Power Query's pattern recognition or transition splits to separate the data.

Power Query cannot automatically distinguish between names and phone numbers without a defined rule. Identifying patterns (such as where letters transition to numbers) is critical for a successful extraction.

Load Data into Power Query

Select your range of mixed contact data and navigate to Data > From Table/Range to open the Power Query Editor.

Use Column From Examples

Navigate to the Add Column tab and click 'Column From Examples'. Type the desired extracted name in the first few rows so Power Query can detect the extraction pattern.

Split by Character Transition

Alternatively, right-click the column header, select 'Split Column', and choose 'By Non-Digit to Digit'. This separates the text (names) from the numbers (phone numbers) if they appear sequentially.

Clean Formatting

Select the newly separated columns, right-click, and choose Transform > Trim and Transform > Clean to remove any lingering irregular spaces or non-printable characters.

Close and Load

Click 'Close & Load' on the Home tab to export the cleaned data into a new worksheet, which can then be saved as a CSV for Outlook.

Define Transformation Rules and Extract via Column From Examples

Provide Sufficient Examples: If using 'Column From Examples', you may need to type out 4 or 5 different examples if the data has highly varied separators so the algorithm can learn the correct logic.

Solution 2

Use Flash Fill for Pattern Recognition

If Power Query rules are too complex to define for highly inconsistent data, Excel's Flash Fill can intuitively recognize and extract data patterns directly in the worksheet.

Clean and Organize Contact Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful, easy-to-use data tools like intelligent Flash Fill and advanced Text to Columns to help you separate names and phone numbers seamlessly for Outlook or CSV exports.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your disorganized contact list.
2. Type the Desired Output: In the adjacent blank column, manually type the correctly extracted name for the first row.
3. Apply Flash Fill: Select the cell below and press Ctrl+E or go to Data > Flash Fill to automatically extract the remaining names.
4. Extract Phone Numbers: Repeat the manual entry and Flash Fill process in the next column to extract the phone numbers.
5. Export as CSV: Once your data is cleanly separated into columns, go to Menu > Save As, and select 'CSV (Comma delimited)' to prepare it for Outlook import.

Intelligent Flash Fill (Ctrl+E) for quick, pattern-based text extractionFully compatible with Microsoft Excel formats (.xlsx, .csv)Comprehensive data cleaning and formatting tools built-inFree, lightweight, and user-friendly interface for daily data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard Text to Columns fail on my phone number data?

Text to Columns relies on consistent delimiters like a comma, tab, or space. If your dataset mixes spaces, dashes, and brackets irregularly, the tool will split the data at the wrong places, resulting in misaligned columns.

Can Power Query automatically distinguish between a name and a label?

No, Power Query does not have semantic AI to know what a word means. It relies on structural rules, such as character types (letters vs. numbers), length, or specific delimiters to separate the data.

How do I ensure my cleaned CSV imports correctly into Outlook?

Before saving as a CSV, ensure your columns have clear headers exactly matching Outlook's fields, such as 'First Name', 'Last Name', and 'Mobile Phone'. During the Outlook import wizard, click 'Map Custom Fields' to double-check that your columns align with Outlook's address book structure.

Excel & Data Problems