我在这里有一个Excel表，其中包含大量数据，但所有"实体"（它的客户测试数据）都被压缩到一个单元格中。我看过其他回答的问题，但它们都没有解决我的问题。这是我拥有的数据的一个示例（有很多）：
{"Url":"https://portal.freemarketfx.com/User/RegisterIndividual","Method":"POST","Form":"{"__RequestVerificationToken":"yBPOicSq9wwIFC4IgI_CDB8RSrJonaRM4ztEo0_1pygdrrIMVP12ivGDF7-bvoIGXIc1vsTSQjB_UKAA3t32AcW9ULbuKdHSFD8TQRPnW9jRj5CBj1Pe9tund8mPUG8Eqjutr-EQ3tMnVsSdrXDkfw2","PostbackData":"{}","Title":"Mr","FirstName":"Sebastien","YourDateofBirth":"31/01/1983","Gender":"Male","MiddleName":"Pascal Emmanuel","IDNumberType":"Passport","IDNumber":"11CX03708","Surname":"Py","AddressList.Index":"0","AddressList[0].SinceYear":"2015","AddressList[0].SinceMonth":"3","AddressList[0].SinceDay":"25","AddressList[0].UntilYear":"2017","AddressList[0].UntilMonth":"3","AddressList[0].UntilDay":"25","AddressList[0].HouseNumber":"05 06","AddressList[0].HouseName":"","AddressList[0].FlatNumber":"","AddressList[0].Street":"29 Adam Road","AddressList[0].CityTown":"Singapore","AddressList[0].Postcode":"289901","AddressList[0].County":"","AddressList[0].District":"","AddressList[0].Country":"SG","ChannelList.Index":"0,1","ChannelList[0].ChannelType":"Email","ChannelList[0].Address":"sebastienpy@gmail.com","ChannelList[0].TelephoneConnectionType":"Cellular","ChannelList[0].TelephonePurpose":"Personal","ChannelList[0].CountryCallingCode":"","ChannelList[0].TelephoneNumber":"","ChannelList[1].ChannelType":"Telephone","ChannelList[1].Address":"","ChannelList[1].TelephoneConnectionType":"Cellular","ChannelList[1].TelephonePurpose":"Personal","ChannelList[1].CountryCallingCode":"65","ChannelList[1].TelephoneNumber":"87275217","PartyIdentificationType":"BicIbanAccountNumber","BankBranchCode":"","Bic":"HSBCSGSG143","AccountNumber":"583474496","AccountSetupDate":"","ReasonForExchange":"TransferToOwnAccount","ReasonForExchangeOther":"","MaxPaymentAmount":"0","MaxPaymentAmountCurrency":"EUR","CurrenciesRequired":"EUR,USD,SGD","FrequencyOfTransfer":"Annual","AnnualTransactionsValue":"0","AnnualTransactionsValueCurrency":"GBP","Destinations":"FR","Referrer":"","AcceptTerms":"true,false"}","Referrer":"https://portal.freemarketfx.com/User/RegisterIndividual","User Agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Safari/537.36","entityId":"1","SendEmailRegistration":"{"Email":"registrations@freemarketfx.com","Data":{"FirstName":"Sebastien Py","Subject":"Registration complete"}}","ActiveAccount":""}
我的问题是有什么方法可以将所有这些数据整理到每个数据的单元格中？请记住，还有数百个其他单元格，所以我不想手动浏览每个细胞。这些数据的很大一部分看起来非常像Python词典，我打算编写一件代码来利用这一点，但是我不知道Excel中是否有更快的内置功能？P.S由于数据因一个客户而异，因此数据将不相同（以防您要使用= MID，=右或=左功能）
看起来像"，它是数据集之间的主要定界符，"："作为次要的定界符。即 "Url":"https://portal.freemarketfx.com/User/RegisterIndividual","Method":"POST",... -a"，"拆分不同类型的数据（URL，方法等）和一个结肠"："从数据类型分配值（例如，URL的值为https：//portal ...）
因此，很好的是获取原始实体数据，将每个逗号的原始数据分开，然后将数据值从其各自的标题分开，将其分为数据类型。在Excel中有几种方法可以做到这一点。如果您不介意VBA，请看一下；excel" unconcatenate"/爆炸函数/爆炸/转换为array
如果您想使用公式，那么基本想法将在B1中；=LEFT(A1,SEARCH(",",A1))"之前发生的任何内容（ A1持有原始数据）然后在A2中;=SUBSTITUTE(A1,B1,"",1)切碎原始数据的左侧，然后将a和b向下拖动，直到b包含每个数据类型
最后，在C1和D2中具有
=LEFT(B1,SEARCH(":",B1))和=SUBSTITUTE(B1,C1,"",1)分别将数据类型切成标题和值
拖动和列C＆amp; d将使您的所有原始数据拆分。对于VBA方法来说，这是一个类似的故事，对于两者，您可能必须使用TRIM和SUBSTITUTE稍微清理结果才能删除垃圾。
希望这很清楚！