Split Key-Value Text into Excel Columns with Power Query
Question details
Split delimited key-value strings into one column per unique key and place each value in the appropriate row.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A text column contains records such as name=Jake||Language=en, with pairs separated by || and keys separated from values by =.
- Observed behavior
- The desired result has a column for each key, with values aligned to their original records and blank cells for missing keys.
Make sure each source row contains one record and uses || between pairs and = between each key and value. Check that a key does not occur more than once within the same record, because pivoting without aggregation requires unique key-value pairs per row.
Split and pivot the text in Excel Power Query
Use Power Query to create columns from the keys while preserving the original record for each value.
Select the text column, choose Insert > Table, and then select Data > From Table/Range to open Power Query Editor.
In Power Query Editor, choose Add Column > Index Column > From 0. The index keeps separate records distinct during the pivot.
Select the text column and choose Home > Split Column > By Delimiter. Enter || as the custom delimiter, expand Advanced options, select Split into Rows, and confirm.
Select the resulting text column and choose Split Column > By Delimiter. Use = as the delimiter and split at the left-most delimiter, then rename the resulting columns Key and Value.
Select the Key column and choose Transform > Pivot Column. Select Value as the Values Column and choose Don't Aggregate under Advanced options, then confirm.
Remove the Index column if you no longer need it, then choose Home > Close & Load to return the transformed table to Excel.

Extract Key-Value Fields with WPS Spreadsheets
Use headers and a formula in WPS Spreadsheets to extract values for known keys from each delimited record. Add a header for each unique key you want to include.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook. Place one delimited record per row in column A.
- 2. Enter key headers: In row 1, enter each unique key you want to extract, starting in B1, such as name and Language. Match the spelling and capitalization used in the source text.
- 3. Enter the extraction formula: In B2, enter =IFERROR(MID("||"&$A2&"||",FIND("||"&B$1&"=","||"&$A2&"||")+LEN(B$1)+3,FIND("||","||"&$A2&"||",FIND("||"&B$1&"=","||"&$A2&"||")+LEN(B$1)+3)-(FIND("||"&B$1&"=","||"&$A2&"||")+LEN(B$1)+3)),"").
- 4. Fill the result: Drag the fill handle across the key columns and down the records. Missing keys display as blank cells; if a key appears more than once in a record, the formula returns its first value.

Frequently Asked Questions
How do I keep values from different records on separate rows?
Before splitting the text in Power Query, choose Add Column > Index Column. Keep the index through the pivot so each original record remains distinct.
Will a value containing an equals sign be preserved?
Yes. In Power Query, split each pair by = at the left-most delimiter so any later equals signs remain in the value.
What happens if a record does not contain one of the keys?
After the Power Query pivot, the corresponding cell is null. In the WPS formula approach, a missing key displays as a blank cell.
Can I update the Power Query result when new source rows are added?
Add the records to the source Excel table, then use Data > Refresh All. Power Query reruns the transformation and includes keys found in the refreshed data.




