Split Variable-Length Codes into Fields with Excel Power Query
Question details
Separate codes with variable-length text and numeric segments into location, employee ID, plant ID, and pay-period fields.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The source column contains values such as AUS177-4M, Canada551-3W, and ENG342-10Mo.
- Observed behavior
- The position of each segment changes because location names, employee IDs, plant IDs, and pay-period text have different lengths.
Make sure the codes follow the same pattern: location text followed by an employee number, a hyphen, a plant number, and pay-period text. Convert the source range to an Excel table so Power Query can load it correctly.
Split the code by character-type transitions in Power Query
Use this method when the code segments vary in length but always change from text to numbers or from numbers to text in the same order.
Power Query can split at the hyphen and at boundaries where character types change. This avoids fixed-position formulas that can fail when a location or pay-period value has more characters.
Select any cell in the code list, then choose Data > From Table/Range. Confirm the table range and select OK to open Power Query Editor.
Select the code column, choose Home or Transform > Split Column > By Delimiter, select Custom as the delimiter option, enter a hyphen (-), and split at the left-most delimiter. This creates a location-and-employee column and a plant-and-period column.
Select the location-and-employee column, choose Transform > Split Column > By Digit to Non-Digit, and select the Non-Digit to Digit transition. Rename the resulting columns Location and Employee ID.
Select the plant-and-period column, choose Transform > Split Column > By Digit to Non-Digit, and select the Digit to Non-Digit transition. Rename the resulting columns Plant ID and Pay Period.
Check that AUS177-4M becomes AUS, 177, 4, and M, then choose Home > Close & Load to return the results to an Excel worksheet.
Use Excel 365 formulas when Power Query is not required
Use formulas when you need the separated fields to update immediately beside each source code without refreshing a Power Query query.
Separate Variable-Length Code Parts with WPS Spreadsheets Flash Fill
WPS Spreadsheets can quickly separate repeated code patterns into individual columns with Flash Fill. Enter the desired result for a few examples, then let WPS detect and apply the pattern to the remaining rows.
- 1. Place codes in one column: Open the workbook in WPS Spreadsheets and place the original codes in column A with a header such as Code.
- 2. Add output column headers: Create adjacent headers for Location, Employee ID, Plant ID, and Pay Period in columns B through E.
- 3. Enter the first expected result: For AUS177-4M, manually enter AUS, 177, 4, and M in the corresponding output cells on the first data row.
- 4. Provide another pattern example: On the next row, enter the expected parts for Canada551-3W as Canada, 551, 3, and W so Flash Fill can identify the variable-length pattern.
- 5. Apply Flash Fill to each output column: Select the cells below an example in an output column, then choose Data > Flash Fill. Repeat for the remaining output columns and verify the generated results.
Frequently Asked Questions
Why do fixed-position formulas fail for codes such as Canada551-3W?
Fixed-position formulas assume each segment has the same number of characters. Canada has more letters than AUS, and 10Mo has more characters than 4M, so splitting at text-to-number boundaries is more reliable.
Which Power Query split option separates AUS from 177?
Select the column containing AUS177, then use Transform > Split Column > By Digit to Non-Digit and choose the Non-Digit to Digit transition.
How do I split 10Mo into plant ID 10 and pay period Mo in Power Query?
Select the column containing 10Mo, choose Transform > Split Column > By Digit to Non-Digit, and use the Digit to Non-Digit transition.
Can Power Query refresh the separated fields when new codes are added?
Yes. Add the new rows to the original Excel table, then choose Data > Refresh All. Power Query reruns the same split steps for the new records.
