How to Split Power Query Text When a Number Changes to a Letter in WPS Office
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.

- 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.
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.
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.
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.
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.
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.
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 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. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the concatenated code column.
- 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. 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. Apply and close: After verifying the split values and duplicated row information, click Close & Load to return the transformed table to the worksheet.

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.




