• 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...

  • 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

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
 

Attachments

  • EXAMPLE.xlsx
    9.7 KB · Views: 18
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
 

Attachments

  • SplitExample.xlsx
    11.2 KB · Views: 15
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


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.
 
luis_marques
Is Your sample result correct?
If no needed formula reply (?)
then with this would give an answer ...
 

Attachments

  • EXAMPLE.xlsb
    17.9 KB · Views: 4
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: ..................
Could you help me?
Thanks in advance.
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:
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.
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.
 
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.
 

Attachments

  • String to table (PB).xlsx
    12.6 KB · Views: 4
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.
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?
 

Attachments

  • EXTRACTION.xlsx
    308.9 KB · Views: 6
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?
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).
 
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).

Yes, indeed i missed. I corrected, but extracted just one set of information. The second set or more the formula doesn´t extract.
 
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

Thanks Bosco. How could be using VBA?
 
Yes, indeed i missed. I corrected, but extracted just one set of information. The second set or more the formula doesn´t extract.
You need to remove the $ marks as appropriate to your setup. See attached file.
 

Attachments

  • EXTRACTION_formula applied.xlsx
    71 KB · Views: 3
luis_marques
Many thing are possible...
Do a new sample file which has that case,
where are data and where would come results?
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.
 

Attachments

  • SAMPLE.xlsx
    23 KB · Views: 4
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.
 

Attachments

  • String to table (PB).xlsx
    26.9 KB · Views: 6
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.
Thanks. It is a very interesting approach to display such data.
 
Back
Top