如何从字符串Excel公式中提取文本不起作用 [英] how to extract a text from a string excel formula not working

问题描述

我正在尝试提取ID，发行版等.搜索功能不起作用:(

I am trying to extract the Id, the release.. etc, and the search function is not working :(

所以我需要公式来查找发行版，并在发行后和逗号之前获取字符，依此类推

so I need the formula to look for the release and get the characters after it and before the comma, so on

这是两行的值，如您所见，它们是随机排列的

here is the value of the 2 rows, and as you can see they're in a random arrangement

这在包含{}

{"id":"C0001"，品牌":"BMW"，价格":"21868,41 $"，发布":"2018"，名称":"BMW 2系列"，类型":"COUPES"}

{"id":"C0001","brand":"BMW","price":"21868,41 $","release":"2018","name":"BMW 2 Series","type":"COUPES"}

'''

推荐答案

将列标题放在B1:G1中.

Put the column headers in B1:G1.

将您的字符串放在A2/中.

Put your String in A2/.

将其放入B2并复制:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,"{""",""),"""}",""),""",""",""":"""),""":""","</b><b>")&"</b></a>","//b[preceding::*[1]='"&B$1&"']")

这篇关于如何从字符串Excel公式中提取文本不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！