Separate data in a cell to different cells

[klllmmm]

I have a data set in below type in a column. How can I extract & put them into separate columns.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 242"]
<tbody>[TR]
[TD]Code:1+Name:Book+Size:200pg+Price:100+Vendor:Abooks[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 242"]
<tbody>[TR]
[TD]Code:2+Name:Pen+Size:Standard+Price:10+Vendor:Abooks[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 242"]
<tbody>[TR]
[TD]Code:3+Name:Bag+Size:Standard+Price:250+Vendor:TopBags[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I worked to some extent using below formula.
MID(A1,1,FIND("+",A1)-1) = Code:1 , MID(Q7,FIND("+",A1,1)+1,FIND("+",A1,2)) = Name:Bo

I want to get the data into separate cells as follows;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code:1<strike></strike>
[/TD]
[TD]Name:Book<strike></strike>
[/TD]
[TD]Size:200pg<strike></strike>
[/TD]
[TD]Price:100<strike></strike>
[/TD]
[TD]Vendor:Abooks<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Code:2<strike></strike>
[/TD]
[TD]Name:Pen<strike></strike>
[/TD]
[TD]Size:Standard<strike></strike>
[/TD]
[TD]Price:10<strike></strike>
[/TD]
[TD]Vendor:Abooks<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Code:3<strike></strike>
[/TD]
[TD]Name:Bag<strike></strike>
[/TD]
[TD]Size:Standard<strike></strike>
[/TD]
[TD]Price:250<strike></strike>
[/TD]
[TD]Vendor:TopBags<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Why not use text to columns with + as the delimiter?

 

[klllmmm]

I Prefer to use a formula rather than text to column. Data is not in the same order. It's a large chunk of data set. I want to prepare a data table from this data set. Something similar to this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD] Size [/TD]
[TD]Price [/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Book[/TD]
[TD]200pg[/TD]
[TD]100[/TD]
[TD]Abooks[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pen[/TD]
[TD]Standard[/TD]
[TD]10[/TD]
[TD]Abooks[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bag[/TD]
[TD]Standard[/TD]
[TD]250[/TD]
[TD]Topbags[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much for your time & effort.....

 

[gaz_chops]

You did say -

I want to get the data into separate cells as follows;

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Code:1<strike></strike>[/TD]
[TD]Name:Book<strike></strike>[/TD]
[TD]Size:200pg<strike></strike>[/TD]
[TD]Price:100<strike></strike>[/TD]
[TD]Vendor:Abooks<strike></strike>[/TD]
[/TR]
[TR]
[TD]Code:2<strike></strike>[/TD]
[TD]Name:Pen<strike></strike>[/TD]
[TD]Size:Standard<strike></strike>[/TD]
[TD]Price:10<strike></strike>[/TD]
[TD]Vendor:Abooks<strike></strike>[/TD]
[/TR]
[TR]
[TD]Code:3<strike></strike>[/TD]
[TD]Name:Bag<strike></strike>[/TD]
[TD]Size:Standard<strike></strike>[/TD]
[TD]Price:250<strike></strike>[/TD]
[TD]Vendor:TopBags[/TD]
[/TR]
</tbody>[/TABLE]


What do you mean by "Data is not in the same order"?

 

[gaz_chops]

Try

=MID(A1,FIND(":",A1)+1,1)

=MID(A1,FIND("me:",A1)+3,FIND("+S",A1)-3-FIND("me:",A1))

=MID(A1,FIND("ze:",A1)+3,FIND("+P",A1)-3-FIND("ze:",A1))

=MID(A1,FIND("ce:",A1)+3,FIND("+V",A1)-3-FIND("ce:",A1))

=MID(A1,FIND("or:",A1)+3,99)

[/FONT]
[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]1[/TD]
  [TD="width: 65"]Book[/TD]
  [TD="width: 65"]200pg[/TD]
  [TD="width: 65"]100[/TD]
  [TD="width: 65"]Abooks[/TD]
 [/TR]
 [TR]
  [TD]2[/TD]
  [TD]Pen[/TD]
  [TD]Standard[/TD]
  [TD]10[/TD]
  [TD]Abooks[/TD]
 [/TR]
 [TR]
  [TD]3[/TD]
  [TD]Bag[/TD]
  [TD]Standard[/TD]
  [TD]250[/TD]
  [TD]TopBags[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
[FONT=Lucida Grande]

 

[klllmmm]

Thank you vary much Mr. gaz_chops. Its a perfect solution.

I meant by data not in order means sometimes data appears like this too

Code:1+Name:Book+Size:200pg+Price:100+Vendor:Abooks
Code:2+Size:Standard+Name:Pen+Vendor:Abooks+Price:10
Its all-right. Thanks again

 

[gaz_chops]

Thank you vary much Mr. gaz_chops. Its a perfect solution.

I meant by data not in order means sometimes data appears like this too

Code:1+Name:Book+Size:200pg+Price:100+Vendor:Abooks
Code:2+Size:Standard+Name:Pen+Vendor:Abooks+Price:10
Its all-right. Thanks again

You're welcome, but note that my solution won't work if they are not in the order of your original post.

 

[Peter_SSs]

For items in any order, try this formula copied across and down.

Excel Workbook
	A	B	C	D	E	F
1		Code	Name	Size	Price	Vendor
2	Code:1+Name:Book+Size:200pg+Price:100+Vendor:Abooks	1	Book	200pg	100	Abooks
3	Code:2+Name:Pen+Size:Standard+Price:10+Vendor:Abooks	2	Pen	Standard	10	Abooks
4	Code:3+Name:Bag+Size:Standard+Price:250+Vendor:TopBags	3	Bag	Standard	250	TopBags
5	Code:2+Size:Standard+Name:Pen+Vendor:Abooks+Price:10	2	Pen	Standard	10	Abooks
6	Vendor:Abc+Size:Large+Code:201+Price:20.55+Name:Folder	201	Folder	Large	20.55	Abc
Extract Items

 

[bosco_yip]

Another option,

In B2 enter formula, copy across to F2 and all copy down :

=TRIM(LEFT(SUBSTITUTE(MID($A2,FIND(B$1,$A2&B$1)+LEN(B$1)+1,90),"+",REPT(" ",90)),90))

Regards

 

[Peter_SSs]

.. or with one less function call

=REPLACE(LEFT($A2,FIND("+",$A2&"+",FIND(B$1,$A2))-1),1,FIND(B$1,$A2)+LEN(B$1),"")