Power Query Problems

Split Key-Value Text into Excel Columns with Power Query

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Split delimited key-value strings into one column per unique key and place each value in the appropriate row.

Split Key-Value Text into Excel Columns with Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Load the source table

Select the text column, choose Insert > Table, and then select Data > From Table/Range to open Power Query Editor.

2
Add a row index

In Power Query Editor, choose Add Column > Index Column > From 0. The index keeps separate records distinct during the pivot.

3
Split pairs into rows

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.

4
Separate keys and values

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.

5
Pivot the keys

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.

6
Load the result

Remove the Index column if you no longer need it, then choose Home > Close & Load to return the transformed table to Excel.

Split and pivot the text in Excel Power Query
Missing or repeated keys: A missing key produces a null value for that record. If a record contains the same key more than once, resolve the duplicates or choose an appropriate aggregation before pivoting.
Work with key-value text in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook. Place one delimited record per row in column A.
  2. 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. 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. 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.
Extract values from key-value text into separate columnsOpen and save Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a straightforward interface
microsoft office alternative - wps office

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.