Power Query Problems

Extract CSV Key-Value Tags into Excel Columns

Elise WilliamsElise Williams Nov 18, 2026 869 views

Question details

Extract named fields from CSV text into separate Excel columns, including records with missing fields.

Extract CSV Key-Value Tags into Excel Columns
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A CSV column contains text such as @{First Name=John; Middle Name=Joe; Last Name=Boxer}, and each name field should become a column.
Observed behavior
Each input record should produce one row, with field names as headers and missing values left blank.
Before you start

Identify the CSV column containing the tags and confirm that each field is separated by a semicolon and each name is separated from its value by an equals sign. The steps below assume each field name appears no more than once per record.

Solution 1Recommended

Split and pivot the tags with Excel Power Query

Use this method to transform multiple CSV records into rows with named columns, even when individual fields are missing.

1
Import the CSV

In Excel, select Data > From Text/CSV, choose the file, and select Transform Data. Check that the tag text appears in a single column.

2
Add a record identifier

In Power Query Editor, select Add Column > Index Column > From 1. Keep the index and tag-text columns so each original record can be reconstructed after splitting.

3
Remove the wrapper

Select the tag-text column, then use Transform > Extract > Text Between Delimiters. Enter @{ as the start delimiter and } as the end delimiter.

4
Split tags into rows

With the text column selected, choose Transform > Split Column > By Delimiter. Select a semicolon, open Advanced options, and choose Split into Rows.

5
Separate names and values

Select the split text column and choose Split Column > By Delimiter. Use the equals sign and split at the left-most delimiter. Rename the resulting columns Field and Value, then select both and use Transform > Format > Trim.

6
Pivot and load

Select the Field column and choose Transform > Pivot Column. Set Values Column to Value and, under Advanced options, choose Don't Aggregate. Select Home > Close & Load to return the results to Excel.

Split and pivot the tags with Excel Power Query
Missing fields and duplicate names: A missing field is blank for that record if the field appears elsewhere in the data. If a field never appears in the CSV, add its column separately. Duplicate field names within one record must be resolved before using Don't Aggregate.
Work with CSV tags in WPS

Extract named fields with WPS Spreadsheets

Open the CSV in WPS Spreadsheets and use headers and formulas to extract known tag names into separate columns. Missing fields return blank cells.

  1. 1. Open the CSV: In WPS Spreadsheets, select Menu > Open and choose the CSV. These steps assume the tag text starts in cell A2.
  2. 2. Normalize the tag text: In B2, enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@{",""),"}",""),"; ",";") to remove the wrapper and a single space after each semicolon.
  3. 3. Add field headers: Enter First Name, Middle Name, and Last Name in C1, D1, and E1. The headers must match the field names in the tag text.
  4. 4. Extract the values: In C2, enter =IFERROR(TRIM(MID(";"&$B2&";",FIND(";"&C$1&"=",";"&$B2&";")+LEN(C$1)+2,FIND(";",";"&$B2&";",FIND(";"&C$1&"=",";"&$B2&";")+1)-FIND(";"&C$1&"=",";"&$B2&";")-LEN(C$1)-2)),""). Drag C2 across to E2, then fill B2:E2 down for the remaining records.
  5. 5. Save the result: Select Menu > Save As and choose an Excel workbook format such as XLSX to keep the extracted columns and formulas.
Extract First Name, Middle Name, and Last Name even when a field is missingOpen CSV files and save results in Microsoft Excel XLSX formatUse familiar spreadsheet formulas and a straightforward interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why should I add an index before splitting the tags?

Add Column > Index Column gives each source record an identifier. Power Query can then group its split fields back into the correct row when you pivot.

What happens if a record has no Middle Name tag?

The Middle Name cell is blank after the pivot, provided that field appears in at least one other record. If it never appears, add the column separately.

Can a value contain an equals sign?

Yes. In Power Query, use Split Column > By Delimiter and choose the left-most equals sign, so subsequent equals signs remain in the value.

Why does the Power Query pivot report an error?

With Don't Aggregate selected, duplicate field names within the same indexed record can cause an error. Inspect the split rows and resolve duplicate names before pivoting.