• 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

luis_marques

luis_marques

Member
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
bosco_yip said:
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
Click to expand...


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
luis_marques said:
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.
Click to expand...
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:
luis_marques said:
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.
Click to expand...
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
shrivallabha said:
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.
Click to expand...
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
luis_marques said:
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?
Click to expand...
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).
 
shrivallabha said:
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).
Click to expand...

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

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

Attachments

  • EXTRACTION_formula applied.xlsx
    71 KB · Views: 3
vletm said:
luis_marques
Many thing are possible...
Do a new sample file which has that case,
where are data and where would come results?
Click to expand...
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
Peter Bartholomew said:
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.
Click to expand...
Thanks. It is a very interesting approach to display such data.
 
You must log in or register to reply here.
Back
Top