CODEWITHSUNDEEP
excelexcel-formula
Hany Shaker
Hany Shaker

Reputation: 27

how to extract a text from a string excel formula not working

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

This whats exactly in the cell values including the {}

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

'''enter image description here

Upvotes: 0

Views: 284

Answers (3)

OlimilOops
OlimilOops

Reputation: 6797

let's assume: A2 contains your string, A3 the next string etc..
then
b1 contains the string: id
e1 contains the string: brand
h1 contains the string: price
k1 contains the string: release
n1 contains the string: name
r1 contains the string: type

cell b2 contains: =SEARCH(B$1; $A2)+LENGTH(B$1)+3
cell c2 contains: =SEARCH(""""; $A2; B2)
cell d2 contains: =MID($A2;B2;C2-B2)

cell e2 contains: =SEARCH(E$1; $A2)+LENGTH(E$1)+3
cell f2 contains: =SEARCH("""";$A2;E2)
cell g2 contains: =MID($A2;E2;F2-E2)

and so on . . .

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can also do this using Power Query, available in Excel 2010+

  • each of your lines is a valid json
  • Load the worksheet range into PQ with Data/Get&Transform/From Table/Range
  • From the PQ UI, you can

    • Add Custom Column and Name it json:

       Formula: =Json.Document([Column1])

    • Add another custom column

      Formula: =Table.FromRecords({[json]})
      • Expand the resultant column of tables
      • Remove the unnecessary columns

M Code

//you may need to change the table name in the second line of code

let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "json", each Json.Document([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Table.FromRecords({[json]})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"id", "brand", "price", "release", "name", "type"}, {"id", "brand", "price", "release", "name", "type"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"json"})
in
    #"Removed Columns1"

enter image description here

note that the columns will match up properly and, in PQ, you'll be able to set your desired order

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

Put the column headers in B1:G1.

Put your String in A2/.

Put this in B2 and copy over:

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

enter image description here

Upvotes: 2

Related Questions