excel函数获取excel中json中的值

存在一份这样的json，作为excel的一列，现在需要获取其中一个字段的值。

{"cloud_account": {"provider": {"region": "\u5317\u4eac", "vendor": "BAIDU"}, "settings": {"deployKey": "fslogin.pem"}, "name": "", "fastone": false, "authPayload": "{\"ACCESS_KEY_ID\": \"sssss\",\"ACCESS_KEY_SECRET\": \"50c36c10734d4c42ae2e47ad7c254d99\"}", "authType": ""}, "cluster_owner": "", "created_by": 0, "fastone": false, "extra_hosts": [], "ldapUri": "ldap://10.0.32.87:389", "ldapBase": "dc=fastonetech,dc=com", "ldapBinddn": "cn=readonly,dc=fastonetech,dc=com", "ldapBindpw": "iHjsQJWUtrJ4H9Vk", "remoteAccesses": [], "mounts": [{"device": "fa.sssss.svc.cluster.local", "fsType": "sftp", "mountpoint": "U", "opts": "", "password": "", "username": "ddddd", "privateKey": "xxxx"}], "nis": {"nis_domain": "", "nis_enabled": "", "nis_server_address": ""}, "price": 0, "providers": [{"keyName": "fslogin.pem", "provider": "BAIDU", "region": "\u5317\u4eac", "vpcId": "vpc-5hruwf7if9ct", "templates": [{"disableHt": false, "enablePublicIP": true, "fsType": [{"name": "b1.c1.16"}], "name": "desktop", "subnets": ["sbn-0rtiyvgsvxab"], "type": [{"name": "bcc.c5.c16m32"}], "volumes": [{"device_name": "sda1", "size": 50, "mount_point": "", "iops": 0}], "role": "COMPUTE"}]}], "scheduler": {"name": "none_windows", "queues": ["desktop"]}, "scheduler_params": {"slurmdbd_host": "", "slurmdbd_port": "", "slurmdbd_address": "", "enforce_limits": false, "accounting": false, "lsf_entitlement_path": ""}, "securityGroups": [], "startupMode": "MANUAL", "autoScaleGroup": [], "apiInfo": "", "autoScale": false, "tags": {".TYPE": "DESKTOP_CLUSTER", ".USER_ID": "3081", ".TENANT_ID": "sssss", ".ID": "6746", ".NAME": "Materials-Studio-2020-Window", ".USERNAME": "sssss", "TYPE": "DESKTOP_CLUSTER", "USER_ID": "3081", "TENANT_ID": "sssss", "ID": "6746", "NAME": "Materials-Studio-2020-Window", "USERNAME": "sssss"}, "schedulerSystem": "none_windows"}   

提取".TENANT_ID"的字符串值的公式：

=MID(C1, FIND("""TENANT_ID"": """, C1) + LEN("""TENANT_ID"": """), FIND("""", C1, FIND("""TENANT_ID"": """, C1) + LEN("""TENANT_ID"": """)) - (FIND("""TENANT_ID"": """, C1) + LEN("""TENANT_ID"": """)))

提取".ID"的字符串值的公式：

=MID(C1, FIND("""ID"": """, C1) + LEN("""ID"": """), FIND("""", C1, FIND("""ID"": """, C1) + LEN("""ID"": """)) - (FIND("""ID"": """, C1) + LEN("""ID"": """)))

要提取 “fsType” 中的 “name” 值，您可以使用以下公式：

=MID(C1, FIND("""fsType"": [{""name"": """, C1) + LEN("""fsType"": [{""name"": """), FIND("""", C1, FIND("""fsType"": [{""name"": """, C1) + LEN("""fsType"": [{""name"": """)) - (FIND("""fsType"": [{""name"": """, C1) + LEN("""fsType"": [{""name"": """)))

该公式假设 JSON 数据位于 C1 单元格中。请根据实际情况调整公式中的单元格引用。此公式将提取 “fsType” 中的 “name” 值，返回结果为 “b1.c1.16”。

获取 “type”: [{“name”: “bcc.gn3.c10m80.1v100-32g”}]中的name的值：

=MID(C1, FIND("""type"": [{""name"": """, C1) + LEN("""type"": [{""name"": """), FIND("""", C1, FIND("""type"": [{""name"": """, C1) + LEN("""type"": [{""name"": """)) - (FIND("""type"": [{""name"": """, C1) + LEN("""type"": [{""name"": """)))

该公式假设 JSON 数据位于 C1 单元格中。请根据实际情况调整公式中的单元格引用。此公式将提取 “type” 中的 “name” 值，返回结果为 “bcc.gn3.c10m80.1v100-32g”。