1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract data from single cell to multiple columns

Discussion in 'Ask an Excel Question' started by luis_marques, Jul 9, 2018.

  1. luis_marques

    luis_marques Member

    Messages:
    58
    Hi ,

    I need a formula to read a single cell and extract data to multiple columns like the attached example. Some criteria:

    1. Data between comma and colon
    2. Remove quotes
    3. Remove brackets
    4. Remove braces

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,998
    1] Data in A1,

    2] In A5, copied across right until blank :

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"[{",","),"}]",","""),""":",","""),"},{",","),",""",REPT(" ",LEN($A1))),COLUMN(A1)*LEN($A1),LEN($A1)))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  3. luis_marques

    luis_marques Member

    Messages:
    58
    Many thanks Bosco.
    Last edited by a moderator: Jul 11, 2018
  4. luis_marques

    luis_marques Member

    Messages:
    58

    Bosco Hi, it works very well if i have two until three products (codes), but it doesn´t work if i have more than 3. What could be possible to deal with such problem?

    Every new product is recognized when you see: },{

    One example of set of products in just a single cell:
    [{"linha":6,"quantidade":1,"preco_cheio":99.9000,"preco_promocional":null,"preco_venda":99.9000,"preco_custo":null,"produto_id":27618983},{"linha":5,"quantidade":1,"preco_cheio":55.0000,"preco_promocional":null,"preco_venda":55.0000,"preco_custo":27.0000,"produto_id":23749136},{"linha":4,"quantidade":1,"preco_cheio":55.0000,"preco_promocional":null,"preco_venda":55.0000,"preco_custo":27.0000,"produto_id":23749884},{"linha":3,"quantidade":1,"preco_cheio":69.9000,"preco_promocional":55.9000,"preco_venda":55.9000,"preco_custo":32.0000,"produto_id":25190708},{"linha":2,"quantidade":1,"preco_cheio":64.9000,"preco_promocional":null,"preco_venda":64.9000,"preco_custo":32.0000,"produto_id":27616965},{"linha":1,"quantidade":1,"preco_cheio":79.9000,"preco_promocional":59.9000,"preco_venda":59.9000,"preco_custo":20.0000,"produto_id":25958161}]

    Could you help me?

    Thanks in advance.
  5. luis_marques

    luis_marques Member

    Messages:
    58
    Anyone could help?
  6. vletm

    vletm Excel Ninja

    Messages:
    4,289
    luis_marques
    Is Your sample result correct?
    If no needed formula reply (?)
    then with this would give an answer ...

    Attached Files:

  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,998
    Hi,

    1] Generally using Text category function to extract/split data, the text length is limited to 255 characters. (as per post #.2 formula)

    2] Your post #.4 example data in text length 844 characters which appear exceeded the limits, so the post #.2 formula unable to work.

    3] The workaround is to use other category function - FILTERXML function, but it required Excel 2013 or above. Otherwise, VBA is your only choice.

    Regards
    Bosco
    Last edited: Oct 10, 2018
    luis_marques likes this.
  8. Haz

    Haz Active Member

    Messages:
    114
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,917
    See if following modified approach to Bosco's formula helps your cause.

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"[","|"),"]","|"),"{",""),"}",""),"""",""),":","|"),",","|"),"|",REPT(" ",LEN($A$1)),COLUMNS($A$1:A1)+1),"|",REPT(" ",LEN($A$1)),COLUMNS($A$1:A1)),LEN($A$1),LEN($A$1)))

    It should work for ~10922 characters in principle.
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    I haven't got the new products sorted (I didn't notice that multiple products were on the line at first) but, starting from lines one per product, I have experimented with tabular output rather than alternating lists.

    Attached Files:

    luis_marques likes this.
  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    Just because it works.
    Not exactly what @luis_marques asked for but it provided an interesting challenge!

    Attached Files:

    luis_marques likes this.
  12. luis_marques

    luis_marques Member

    Messages:
    58
    Thanks Shrivallabha!
    I tried to implement, but i don't know whats was wrong. That is a sample of my database. In the yellow cell i typed your formula. Could you verify, please?

    Attached Files:

  13. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,917
    I guess you rewrote formula instead of copying it. You have missed space in yellow cell formula.

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"[","|"),"]","|"),"{",""),"}",""),"""",""),":","|"),",","|"),"|",REPT(" ",LEN($A$1)),COLUMNS($A$1:A1)+1),"|",REPT(" ",LEN($A$1)),COLUMNS($A$1:A1)),LEN($A$1),LEN($A$1)))

    See red marked portions (I have underlined them).
    Thomas Kuriakose likes this.
  14. luis_marques

    luis_marques Member

    Messages:
    58
    Yes, indeed i missed. I corrected, but extracted just one set of information. The second set or more the formula doesn´t extract.
  15. luis_marques

    luis_marques Member

    Messages:
    58
    Thanks Bosco. How could be using VBA?
  16. vletm

    vletm Excel Ninja

    Messages:
    4,289
    luis_marques likes this.
  17. luis_marques

    luis_marques Member

    Messages:
    58
    Many thanks. I didn´t notice. Sorry.
    Is possible to make this VBA operation for many lines at same time and each line extracted to start at the right next column of original ones?
  18. vletm

    vletm Excel Ninja

    Messages:
    4,289
    luis_marques
    Many thing are possible...
    Do a new sample file which has that case,
    where are data and where would come results?
  19. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,917
    You need to remove the $ marks as appropriate to your setup. See attached file.

    Attached Files:

  20. luis_marques

    luis_marques Member

    Messages:
    58
    I attached a sample. Column A, several lines with information that need to be extracted right to next column like in the yellow cells in the sample.

    Attached Files:

  21. vletm

    vletm Excel Ninja

    Messages:
    4,289
    luis_marques
    one possible way
    ... Press [ Do It ]-button
    > You can change Your data 'anytime' and
    > after that ... press again to get results.

    Attached Files:

  22. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    I seem to have missed a change in the input data format in this discussion. Didn't it start with the entire data table in one cell? Now it seems that the initial split into records has already been performed.

    I also fail to understand why an alternating pattern of property name and value should be specified as the output format. Isn't it both awkward to produce and to reference?

    For comparison only, I have created tabular format both by using an array formulas (not that simple) and by using Power Query.

    Attached Files:

    luis_marques likes this.
  23. luis_marques

    luis_marques Member

    Messages:
    58
    Perfect. It worked very well. Many thanks.
  24. luis_marques

    luis_marques Member

    Messages:
    58
    Thanks. It is a very interesting approach to display such data.

Share This Page