Excel

[krishnatklm]

Hi Team,

I need to clean some Excel data like 

EmpDetails

{"Name":"DISHA","Salary":"15600.00","Designation":"Admin","MobileNo":"9699265225"}

I need to extract only the Designation to the next column. 

Please help.

[#2 · Accepted solution]

@krishnatklm 

To extract the "Designation" from the given data and place it in the next column, you can use the following steps:

1. Assuming the given data is in cell A1, enter the following formula in cell B1:

=MID(A1, FIND("Designation", A1) + 14, FIND(",", A1, FIND("Designation", A1)) - FIND("Designation", A1) - 14)

This formula uses the MID, FIND, and COMMA functions to extract the "Designation" value.

1. Press Enter to get the extracted "Designation" value in cell B1.
2. If you have more data in column A that needs extraction, you can drag the formula down in column B to apply it to the respective cells.

The result will be the extracted "Designation" value in column B, separated from the original data. Make sure to adjust the formula if your data is located in a different cell or if there are variations in the structure of the data.

[#3]

Alternatively with Excel365:

=TEXTBEFORE(TEXTAFTER(B1,"Designation"":"""),"""")

or a more 'general' solution to extract various components:

=LET(in,B1,
parsed,TEXTSPLIT(SUBSTITUTE(MID(in,2,LEN(in)-2),"""",""),":",",",FALSE),
XLOOKUP("Designation",INDEX(parsed,,1),INDEX(parsed,,2)))

where 'parsed' becomes a table of the paired values in that JSON and then you can find/lookup/filter what you need (note this assumes no special characters ",: in the values

[#4]

https://techcommunity.microsoft.com/t5/excel/json-to-excel/m-p/3840588#M194045

[#5]

Thanks

[#6]

Thanks

[#7]

@krishnatklm 

Another 365 variant:

=LET(
    matrix, TEXTSPLIT(SUBSTITUTE(A1, """", ""), ":", ","),
    VLOOKUP("Designation", matrix, 2, 0)
)