Parsing variable data into columns not using text to columns

[pmcsherry]

I want to run a text to columns function on the data to parse it from one cell and move it to multiple cells. For example, I want a cell that takes the data inside parentheses and displays into multiple cells (e.g. Ford Ranger, Ford, 2001). The text to columns function doesn't work well since the data isn't even across columns and if you do a delimited text to columns function it makes columns for the data I don't need (e.g. [{model"=>).

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 85"]
<colgroup><col width="85" style="width:85pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 85"][{"model"=>"FORD RANGER", "make"=>"FORD", "year"=>"2001"}, {"year"=>"2011", "make"=>"HONDA", "model"=>"ACCORD"}, {"year"=>"2004", "make"=>"BUICK", "model"=>"REGAL"}][/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 85"]
<colgroup><col width="85" style="width:85pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 85"][{"model"=>"JEEP CHEROKEE", "make"=>"JEEP", "year"=>"2000"}, {"year"=>"2003", "make"=>"TOYOTA", "model"=>"CELICA"}, {"year"=>"2001", "make"=>"JEEP", "model"=>"CHEROKEE"}, {"year"=>"1998", "make"=>"FORD", "model"=>"RANGER"}][/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 85"]
<colgroup><col width="85" style="width:85pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="width: 85"][{"model"=>"SATURN L-SERIES", "make"=>"SATURN", "year"=>"2000"}][/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ideally, I'd like columns that looked like this of the above data but in multiple columns/rows.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1468"]
<colgroup><col><col span="4"><col><col span="9"></colgroup><tbody>[TR]
[TD]Model 1[/TD]
[TD]Make 1[/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Make 2[/TD]
[TD]Model 2[/TD]
[TD]Year 3[/TD]
[TD]Make 3[/TD]
[TD]Model 3[/TD]
[TD]Year 4[/TD]
[TD]Make 4[/TD]
[TD]Model 4[/TD]
[TD]Year 5[/TD]
[TD]Make 5[/TD]
[TD]Model 5[/TD]
[/TR]
[TR]
[TD]TOYOTA PICKUP[/TD]
[TD]TOYOTA[/TD]
[TD="align: right"]1994[/TD]
[TD]2004[/TD]
[TD]FORD[/TD]
[TD]ESCAPE[/TD]
[TD]2001[/TD]
[TD]FORD[/TD]
[TD]F-150[/TD]
[TD]2000[/TD]
[TD]FORD[/TD]
[TD]TAURUS[/TD]
[TD]1994[/TD]
[TD]CHEVROLET[/TD]
[TD]S-10[/TD]
[/TR]
[TR]
[TD]GMC SIERRA 1500[/TD]
[TD]GMC[/TD]
[TD="align: right"]1992[/TD]
[TD]2000[/TD]
[TD]HYUNDAI[/TD]
[TD]SONATA[/TD]
[TD]1988[/TD]
[TD]BUICK[/TD]
[TD]REGAL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]LINCOLN CONTINENTAL[/TD]
[TD]LINCOLN[/TD]
[TD="align: right"]1992[/TD]
[TD]1995[/TD]
[TD]FORD[/TD]
[TD]THUNDERBIRD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is the proper function/formula to parse data from 1 cell to multiple cells and be able to drop unnecessary data?

Thanks,

 

[davesweep]

Hello,

YOu could get rid of all the unwanted characters, and then text to columns using comma as delimiter.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"{",""),"}",""),"[",""),"]",""),CHAR(34)&"make"&CHAR(34)&"=>",""),CHAR(34)&"model"&CHAR(34)&"=>",""),CHAR(34)&"year"&CHAR(34)&"=>",""),CHAR(34),"")

will reduce to a comma delimited string.

 

[pmcsherry]

that works! thanks!

 

[Peter_SSs]

If you wanted to try a direct formula approach, try this, copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	[{"model"=>"FORD RANGER", "make"=>"FORD", "year"=>"2001"}, {"year"=>"2011", "make"=>"HONDA", "model"=>"ACCORD"}, {"year"=>"2004", "make"=>"BUICK", "model"=>"REGAL"}]	FORD RANGER	FORD	2001	2011	HONDA	ACCORD	2004	BUICK	REGAL
3	[{"model"=>"JEEP CHEROKEE", "make"=>"JEEP", "year"=>"2000"}, {"year"=>"2003", "make"=>"TOYOTA", "model"=>"CELICA"}, {"year"=>"2001", "make"=>"JEEP", "model"=>"CHEROKEE"}, {"year"=>"1998", "make"=>"FORD", "model"=>"RANGER"}]	JEEP CHEROKEE	JEEP	2000	2003	TOYOTA	CELICA	2001	JEEP	CHEROKEE	1998	FORD	RANGER
4	[{"model"=>"SATURN L-SERIES", "make"=>"SATURN", "year"=>"2000"}]	SATURN L-SERIES	SATURN	2000
Split Text