Power Query Problems

How to Remove a Middle Initial and Preceding Space in Power Query

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a Power Query transformation formula to identify and remove a middle initial and its preceding space from a string, without affecting strings that lack an initial or contain multiple given names.

Product: Excel Power Query
Device & OS: not provided
Scenario: Cleaning and standardizing a dataset of names where some entries include an unwanted middle initial at the end.
Observed behavior: The goal is to conditionally extract the name string without the trailing middle initial and space, triggering only when the initial is actually present.

Before you start

Ensure your dataset is loaded into the Power Query Editor and check that the target text column has consistent spacing, as hidden trailing spaces can interfere with positional formulas.

Solution 1Recommended

Use a Conditional Custom Column to Remove the Initial

This method uses Power Query's built-in text extraction functions to check the position of the space and conditionally remove the last two characters.

By targeting the character exactly two positions from the end of the text string, you can safely identify if a middle initial and space exist without accidentally splitting multiple given names.

Add a Custom Column

In the Power Query Editor, navigate to the 'Add Column' tab on the top ribbon and click 'Custom Column' to open the formula dialog box.

Write the Conditional Formula

In the Custom Column formula box, write a conditional statement to check if the second-to-last character is a space. For example: if Text.Middle([Name], Text.Length([Name]) - 2, 1) = " " then Text.Start([Name], Text.Length([Name]) - 2) else [Name].

Verify and Apply

Click 'OK' to apply the transformation. Review the new column to verify that names with initials are properly trimmed, while standard names and multiple given names remain completely intact.

Handling Inconsistent Data: If your data contains suffixes like 'Jr.' or trailing spaces, apply the Text.Trim function to your column before running this conditional check to ensure accuracy.

Remove Middle Initials Easily with WPS Spreadsheet Formulas

If you prefer using standard functions rather than Power Query, WPS Office allows you to achieve the same data cleaning results using built-in text formulas. It is highly compatible with Microsoft Excel and completely free to use.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the name list.
2. Apply a text formula: In an empty adjacent column, use a formula like =IF(MID(A2,LEN(A2)-1,1)=" ", LEFT(A2,LEN(A2)-2), A2) to dynamically check for the space.
3. Drag to fill the column: Click the fill handle at the bottom-right of the cell and drag it down to apply the extraction formula to the rest of your dataset.
4. Copy and paste as values: Select the newly calculated column, copy it, and paste it as values over the original data to finalize your clean text.

Free and feature-rich Office suite alternativeFull compatibility with Microsoft Excel formats (.xlsx, .csv)Powerful built-in text formulas for easy data extractionLightweight application that runs smoothly on any device

microsoft office alternative - wps office

Frequently Asked Questions

Can I remove prefixes or suffixes using a similar Power Query formula?

Yes, you can adapt the conditional logic to check the beginning or end of the text string using Text.StartsWith or Text.EndsWith, and then remove the specific characters using Text.Middle or Text.End.

Why isn't my Power Query formula removing the initial as expected?

This usually happens if there are invisible trailing spaces after the initial. Use the Text.Trim function on your column by right-clicking the column header, selecting Transform, and clicking Trim before applying the custom formula.

Is there a non-formula way to split names in Power Query?

Yes, you can use the 'Split Column' feature by delimiter (such as a space) from the Home tab. However, this may incorrectly separate multiple given names into multiple columns, which is why a conditional custom column is better for targeting just the final initial.

Excel & Data Problems