Forum Discussion
Excel
To extract the "Designation" from the given data and place it in the next column, you can use the following steps:
- 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.
- Press Enter to get the extracted "Designation" value in cell B1.
- 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.
To extract the "Designation" from the given data and place it in the next column, you can use the following steps:
- 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.
- Press Enter to get the extracted "Designation" value in cell B1.
- 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.
- Thanks
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
- Thanks
