Forum Discussion

krishnatklm's avatar
krishnatklm
Copper Contributor
Jun 03, 2023
Solved

Excel

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.

excel
  • NikolinoDE's avatar
    NikolinoDE
    Jun 07, 2023

    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.

     

6 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 17, 2023

    krishnatklm 

    Another 365 variant:

    =LET(
    matrix, TEXTSPLIT(SUBSTITUTE(A1, """", ""), ":", ","),
    VLOOKUP("Designation", matrix, 2, 0)
)
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 07, 2023

    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.

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jun 07, 2023

      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

       

Resources