Power Query Problems

How to Split Names and Job Titles with Suffixes in Power Query

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to separate names and job titles from a single column of text, ensuring that name suffixes like PhD, Jr., and III remain attached to the name, while splitting at the first comma for names without suffixes.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up a dataset containing combined names and job titles with variable comma placements due to optional personal and professional suffixes.
Observed behavior: Standard delimiter splits separate suffixes from names when splitting by commas, leading to misaligned data columns because some names have suffixes and others do not.

Before you start

Ensure your dataset is formatted as an Excel Table and create a backup of your original data before applying complex Power Query transformations or dynamic array formulas.

Solution 1Recommended

Use Power Query Conditional Logic to Split Text

Ideal for automated, refreshable data cleanup directly in Power Query.

By utilizing Power Query's M formula language, you can detect if a string contains known suffixes. If it does, you can extract text based on the second comma instead of the first, keeping the suffix attached to the name.

Load Data to Power Query

Select your table in Excel and navigate to Data > From Table/Range to open the Power Query Editor.

Add a Custom Column

In the Power Query ribbon, go to Add Column > Custom Column.

Apply Conditional Split Logic

Write an M formula that checks if the string contains specific suffixes (e.g., using Text.Contains). If true, instruct the query to split at the second comma; otherwise, split at the first comma.

Extract Substrings

Use Text.BeforeDelimiter and Text.AfterDelimiter coupled with your conditional logic to separate the Name (with suffix) and Job Title into two distinct new columns.

Clean and Close

Remove the original combined column, trim any excess spaces by right-clicking the new column headers and selecting Transform > Trim, and then click Home > Close & Load.

Automatic Refresh: By using Power Query, this transformation will automatically apply to any new data added to your source table upon clicking Refresh.

Solution 2

Use Dynamic Array Formulas (TEXTSPLIT & TEXTAFTER)

A quick formula-based solution for users who prefer working directly in the Excel grid without opening Power Query.

Free Microsoft Office alternative

Handle Complex Data Effectively in WPS Office

While advanced Power Query M-code is specific to Microsoft Excel, WPS Spreadsheet offers powerful data processing capabilities, including advanced text functions, dynamic arrays, and intuitive Text-to-Columns, making it a fast and completely free alternative for your daily data analysis tasks.

1. Download and Install: Download WPS Office from the official website and install it on your device.
2. Open your Dataset: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx) directly.
3. Clean your Data: Use the built-in Text-to-Columns feature located under the Data tab, or utilize powerful text functions to split and organize your records.

Seamless compatibility with Microsoft Excel (.xlsx, .xls, .csv) files without data loss.Fully supports advanced text splitting formulas like TRIM, IFERROR, and robust data cleaning tools.Free, lightweight, and capable of loading massive datasets in seconds.Familiar user interface requiring no learning curve for experienced Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does splitting by comma separate suffixes from names?

When a name includes a suffix like 'John Doe, Jr.', the comma acts as a delimiter. If you perform a standard split by comma, 'Jr.' gets pushed into the next column instead of staying with the name, which misaligns the data alongside names that lack suffixes.

Can I handle multiple different suffixes in one Power Query formula?

Yes, you can use List.AnyTrue alongside Text.Contains in Power Query M-code to check against a predefined list of valid suffixes (e.g., {'Jr.', 'Sr.', 'PhD', 'III'}) and apply the correct delimiter logic dynamically.

Are dynamic array functions like TEXTSPLIT available in older Excel versions?

No, functions like TEXTSPLIT and TEXTAFTER are only available in newer versions of Excel (such as Microsoft 365). For older versions, you must rely on Power Query or a complex combination of LEFT, RIGHT, MID, and FIND functions.

How do I refresh Power Query data once new rows are added?

Simply right-click anywhere inside the loaded output table in your worksheet and select 'Refresh', or go to the Data tab and click 'Refresh All'. The custom splitting logic will automatically apply to the new entries.

Excel & Data Problems