Extract CSV Key-Value Tags into Excel Columns
Question details
Extract named fields from CSV text into separate Excel columns, including records with missing fields.

- 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.
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.
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.
In Excel, select Data > From Text/CSV, choose the file, and select Transform Data. Check that the tag text appears in a single column.
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.
Select the tag-text column, then use Transform > Extract > Text Between Delimiters. Enter @{ as the start delimiter and } as the end delimiter.
With the text column selected, choose Transform > Split Column > By Delimiter. Select a semicolon, open Advanced options, and choose Split into Rows.
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.
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.

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. Open the CSV: In WPS Spreadsheets, select Menu > Open and choose the CSV. These steps assume the tag text starts in cell A2.
- 2. Normalize the tag text: In B2, enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@{",""),"}",""),"; ",";") to remove the wrapper and a single space after each semicolon.
- 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. 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. Save the result: Select Menu > Save As and choose an Excel workbook format such as XLSX to keep the extracted columns and formulas.

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.




