How to Parse and Split JSON Data into Excel Columns

Manually parsing nested JSON strings in Excel is notoriously tedious and error-prone. When consolidating data from standard funding sources, analysts often receive complex API payloads rather than clean tables. Fortunately, mastering Excel formulas grants immediate, dynamic access to structured metrics without external scripts.

Stipulation: This native approach requires modern Excel 365 functions like TEXTSPLIT and TEXTAFTER to successfully isolate keys and values. For instance, converting {"grant_id":"G-99", "amount":50000} into dedicated, clean columns. Below, we break down the exact step-by-step formula architecture to automate your data extraction.

As modern workflows increasingly rely on web APIs, cloud integrations, and system logs, Excel users frequently find themselves dealing with raw JSON (JavaScript Object Notation) data. While JSON is the undisputed standard for data exchange on the web, its nested, key-value structure is notoriously difficult to read and analyze in a standard spreadsheet layout.

If you have pasted or imported JSON payloads into Excel and need to parse them into neat, individual columns, you don't necessarily have to write complex VBA scripts or manually copy-paste values. Depending on your version of Excel, you can use powerful modern formulas, classic text-manipulation formulas, or built-in ETL tools like Power Query to get the job done. This comprehensive guide walks you through the best methods to split JSON data into columns in Excel.

The Sample Data Set

To demonstrate these solutions, let's assume we have raw JSON strings stored in Column A (starting at cell A2). Each row contains a JSON flat object representing a product transaction:

{"ID":101,"Product":"Premium Laptop","Price":1299.99,"Category":"Electronics","InStock":true}

Our goal is to parse this string and split the values of ID, Product, Price, Category, and InStock into separate, labeled columns (Columns B through F).

Method 1: Modern Excel Formulas (Office 365 & Excel 2021+)

If you are using Microsoft 365 or Excel 2021, you have access to highly efficient text manipulation functions: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT. These functions make parsing JSON incredibly straightforward without requiring complex nesting.

1. Extracting Text-Based JSON Values

For values enclosed in quotation marks (like the "Product" or "Category" fields), we can target the key and isolate the string between the surrounding quotes.

To extract the Product name ("Premium Laptop") from cell A2, use the following formula in cell C2:

=TEXTBEFORE(TEXTAFTER(A2, """Product"":"""), """")

How it works:

• TEXTAFTER(A2, """Product"":""") searches the string in A2 for the delimiter "Product":" (expressed with escaped double quotes in Excel) and returns everything after it: Premium Laptop","Price":1299.99,"Category":"Electronics","InStock":true}.
• TEXTBEFORE(..., """") takes that result and extracts everything before the first occurrence of a double quote ("), leaving us with exactly Premium Laptop.

2. Extracting Numeric or Boolean JSON Values

In JSON, numbers and booleans (true/false) are not wrapped in quotation marks. To extract these, we look for the comma (,) or closing curly bracket (}) that marks the end of the value.

To extract the Price (1299.99) from cell A2, write this formula in cell D2:

=VALUE(TEXTBEFORE(TEXTAFTER(A2, """Price"":"), ","))

How it works:

• TEXTAFTER(A2, """Price"":") returns 1299.99,"Category":"Electronics","InStock":true}.
• TEXTBEFORE(..., ",") cuts off everything after the comma, returning the text string 1299.99.
• The VALUE() function converts that extracted text string back into a real number that you can use for calculations, charting, or formatting as currency.

Method 2: Creating a Dynamic, Reusable LAMBDA Function

If you have many JSON columns to extract, typing custom variations of TEXTBEFORE and TEXTAFTER for every key gets repetitive. In Excel 365, you can use the LAMBDA function to build your own custom, reusable formula called GET_JSON_VAL.

Step-by-Step Custom Formula Setup:

1. Go to the Formulas tab on the Excel Ribbon and click Name Manager.
2. Click New to create a new named formula.
3. In the Name field, enter: GET_JSON_VAL.
4. In the Refers to field at the bottom, paste the following formula:

=LAMBDA(json_text, key_name, LET(
    key_pattern, """" & key_name & """:",
    after_key, TEXTAFTER(json_text, key_pattern),
    first_char, LEFT(after_key, 1),
    IF(first_char = """", 
        TEXTBEFORE(RIGHT(after_key, LEN(after_key) - 1), """"), 
        IF(ISNUMBER(FIND(",", after_key)), 
            TEXTBEFORE(after_key, ","), 
            TEXTBEFORE(after_key, "}")
        )
    )
))

Once saved, you can use this brand-new custom function anywhere in your workbook just like a native Excel function! To extract the product category, simply write:

=GET_JSON_VAL(A2, "Category")

To extract the stock status, write:

=GET_JSON_VAL(A2, "InStock")

This single custom formula automatically detects whether the target value is a quoted string, a middle-of-the-string number, or the very last key-value pair in the JSON object ending with a curly bracket.

Method 3: Traditional Excel Formulas (Legacy Excel Versions)

If you are using older versions of Excel (such as Excel 2013, 2016, or 2019) that do not support TEXTBEFORE or TEXTAFTER, you will need to rely on legacy functions like MID, SEARCH, and LEN.

While these formulas look daunting, they follow the same spatial logic of locating the key and slicing the string.

Formula to Extract the "Product" Value (Legacy Version):

=MID(A2, SEARCH("""Product"":""", A2) + LEN("""Product"":"""), SEARCH("""", A2, SEARCH("""Product"":""", A2) + LEN("""Product"":""")) - (SEARCH("""Product"":""", A2) + LEN("""Product"":""")))

Breaking Down the Chaos:

• SEARCH("""Product"":""", A2) + LEN("""Product"":""") locates the exact character index where the actual value starts (immediately after the opening quotes of the product name).
• The second SEARCH finds the closing quote of the product value by starting its search from the beginning of the product value itself.
• Subtracting these two points calculates the exact length of the product name, allowing the MID function to extract only the desired text.

Method 4: The Professional Standard – Power Query

While formulas are great for quick, single-line extractions, they can become slow and hard to maintain if you have thousands of rows or nested JSON structures (e.g., arrays or objects within objects). For robust production pipelines, Power Query is the superior, built-in solution.

How to Parse JSON with Power Query:

1. Select your data range containing the JSON column.
2. Go to the Data tab on the Ribbon and click From Table/Range. This opens the Power Query Editor.
3. Right-click the column header containing your JSON text.
4. Go to Transform > Parse > JSON.
5. Your plain text column will transform into a structured column of "Record" links. Click the Expand icon (two arrows pointing outward) in the column header.
6. Uncheck "Use original column name as prefix", select the keys you want to keep as columns (ID, Product, Price, etc.), and click OK.
7. Go to the Home tab and click Close & Load.

Power Query will automatically create a new, beautifully formatted Excel table. When your raw JSON source data changes, all you have to do is click Refresh All on the Data tab to update the parsed columns instantly.

Formula vs. Power Query: Which Should You Choose?

Conclusion

Parsing JSON in Excel no longer requires writing tedious VBA parsers. For quick, dynamic extractions of flat JSON strings, leverage modern Microsoft 365 functions like TEXTAFTER and TEXTBEFORE, or pack them into a custom LAMBDA function to keep your worksheet neat. If you find yourself cleaning up massive, nested, or production-grade API responses, bypass formulas entirely and let Power Query's visual interface handle the heavy lifting.