Power Query Problems

How to Split Power Query Text When a Number Changes to a Letter in WPS Office

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 868 views

Question details

The user needs to split a concatenated code column at every point where a number character transitions to a letter character, then output each split value on its own row while duplicating the related row information.

Split Power Query Text When a Number Changes to a Letter
Product
WPS Office Spreadsheets - Power Query
Device & OS
not provided
Scenario
A table contains a CONALL column with values such as 123ABC456DEF, and the user wants each split segment on its own row while retaining the existing associated row data.
Observed behavior
The concatenated code remains unsplit, so the number-to-letter boundaries cannot be used for row expansion or further analysis.
Before you start

Confirm that the source column is formatted as Text and that the data is loaded into the Power Query Editor. Identify the exact column name to split, such as CONALL, and note any other columns that must be retained when rows expand.

Solution 1Recommended

Split CONALL by Character Transition and Expand the List to Rows in Power Query

Use this solution when you want a dynamic M-based split that detects number-to-letter boundaries and repeats all other column values for each generated segment.

Power Query can split text based on character type transitions instead of a fixed delimiter. This is useful for codes like 123ABC456DEF because the boundary changes from a digit to a non-digit or letter.

The technique adds a custom column that returns a list of split values, then expands that list into new rows so related row information is preserved.

1
Format the CONALL column as Text

In the Power Query Editor, select the CONALL column. Go to Transform > Data Type and choose Text so all values are treated as text before applying the splitter.

2
Add a custom split list column

Go to Add Column > Custom Column. Name the new column CONSPLIT and enter the formula: = Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([CONALL]). This returns a list of text segments where the character type changes from a number.

3
Expand the CONSPLIT list into new rows

In the new CONSPLIT column, click the expand icon in the column header. Choose Expand to New Rows so each split segment becomes its own row and the other columns are duplicated automatically.

4
Rename and keep the split code column

Rename the expanded column to CONSPLIT if needed, then select the existing CONALL column and choose Remove Column only if the original concatenated code is no longer required. Confirm that other row information is repeated correctly.

Split CONALL by Character Transition and Expand the List to Rows in Power Query
Rows expanded successfully: The CONALL values are split at number-to-letter transitions and the remaining columns are duplicated for each generated CONSPLIT row.

Split Power Query Text Transitions Directly in WPS Office

Use WPS Office Spreadsheets and the built-in Power Query Editor to split concatenated codes at number-to-letter transitions without manual text-to-columns work. The M function creates a list that can be expanded into rows, while other columns are duplicated automatically.

  1. 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the concatenated code column.
  2. 2. Load the data into Power Query Editor: Select the data range, go to Data > Get Data or From Table/Range, and open the Power Query Editor.
  3. 3. Create the character transition split: Confirm the source column is Text, add a custom column with the Splitter.SplitTextByCharacterTransition formula, and expand the resulting list to new rows.
  4. 4. Apply and close: After verifying the split values and duplicated row information, click Close & Load to return the transformed table to the worksheet.
Splits irregular concatenated codes at character-type transitions, not just fixed charactersKeeps other row information intact when the split list is expanded to new rowsUses familiar Power Query workflows that are compatible with Microsoft Excel workbook formatsFree to use and lightweight compared with Microsoft Office for supported Power Query transformations
microsoft office alternative - wps office

Frequently Asked Questions

How does Splitter.SplitTextByCharacterTransition know where to split?

It splits the text when the current character belongs to the first specified set, such as {"0".."9"}, and the next character meets the transition condition, such as not being a digit. If your codes only have number-to-letter boundaries, this creates one split per boundary.

Why should I expand the custom column to rows instead of using Split Column?

Expanding to rows duplicates the other columns for every split segment. Split Column can create multiple columns, which is less suitable when each split value should be in its own row.

Can I use a formula for uppercase and lowercase letters only?

Yes. You can adjust the transition condition to test whether the next character is in {"a".."z"} & {"A".."Z"} so the split occurs only when a number is followed by a letter.

What should I do if the CONALL column is not recognized as text?

Select the column in Power Query Editor and change its Data Type to Text before applying the splitter. This prevents type errors in the custom column formula.

Will WPS Office preserve the other row values after expansion?

Yes. When you expand a list column to new rows, Power Query repeats the values from the other columns for each row generated from that list.