Split up text in columns

[ferocious12]

Hi Guys - can you please help me with the formula. I have following text in column A1. "sellerName" is appearing number of times in the cell text and I have to list the names of all the sellers e.g. BBC, Topup ..... appearing next to "sellerName" in column B. Thanks!

"sellerId":"1020966","mainSellerId":"1020966","sellerName":"BBC","sellerId":"1022147","mainSellerId":"1022147","sellerName":"Topup",​

 

[rahulshewale1]

Hi @ferocious12 ,

See if is it ok ?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TEXT", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"TEXT", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TEXT"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TEXT", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "TEXT", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TEXT.1", "TEXT.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"TEXT.1", type text}, {"TEXT.2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([TEXT.1] = "sellerName"))
in
    #"Filtered Rows"

 

[shrivallabha]

With your sample, following formula can work (copy down):

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$1,""":""",REPT(" ",999),3*ROWS($A$1:$A1)),""",",REPT(" ",999),3*ROWS($A$1:$A1)),999,999))

 

[ferocious12]

Hi @ferocious12 ,

See if is it ok ?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TEXT", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"TEXT", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TEXT"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TEXT", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "TEXT", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TEXT.1", "TEXT.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"TEXT.1", type text}, {"TEXT.2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([TEXT.1] = "sellerName"))
in
    #"Filtered Rows"

Thanks. Mcode is new to me. I need to figure it out and get back

 

[ferocious12]

With your sample, following formula can work (copy down):

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$1,""":""",REPT(" ",999),3*ROWS($A$1:$A1)),""",",REPT(" ",999),3*ROWS($A$1:$A1)),999,999))

Thanks. This works perfectly for this string. However, the original text is too long and there are more than 30 instances of "sellerName" that need to be captured. Is there a formula that search for "sellerName" and trim based on that or word "thumb" and trim "sellerName" (as it always appear before word "thumb" in the text)?

 

[bosco_yip]

Another option,

1] Create a Data Validation dropdown list in C1 with source : sellerId,mainSellerId,sellerName

2] In B2, formula copied down :

=SUBSTITUTE(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A$2,"""",""),",","</b><b>")&"</b></a>","//b[starts-with(.,'"&C$1&"')]["&ROW(A1)&"]"),C$1&":","")

3] Then, click and change the C1 criteria



Regards
Bosco

 

[shrivallabha]

You can also use FILTERXML if you have Excel 2013 or higher.

=FILTERXML("<data>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"""sellerId"":""","<td>"),""",""mainSellerId"":""","</td><td>"),""",""sellerName"":""","</td><td>"),""",","</td>")&"</data>","/data/td["&(ROW()*3)&"]")

See attached demo file.