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"=>).

[{"model"=>"FORD RANGER", "make"=>"FORD", "year"=>"2001"}, {"year"=>"2011", "make"=>"HONDA", "model"=>"ACCORD"}, {"year"=>"2004", "make"=>"BUICK", "model"=>"REGAL"}] <colgroup><col width="85" style="width:85pt"> </colgroup><tbody> <!--StartFragment--> <!--EndFragment--> </tbody>
[{"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"}] <colgroup><col width="85" style="width:85pt"> </colgroup><tbody> <!--StartFragment--> <!--EndFragment--> </tbody>
[{"model"=>"SATURN L-SERIES", "make"=>"SATURN", "year"=>"2000"}] <colgroup><col width="85" style="width:85pt"> </colgroup><tbody> <!--StartFragment--> <!--EndFragment--> </tbody>

<tbody>
</tbody>

Ideally, I'd like columns that looked like this of the above data but in multiple columns/rows.

Model 1 Make 1 Year 1 Year 2 Make 2 Model 2 Year 3 Make 3 Model 3 Year 4 Make 4 Model 4 Year 5 Make 5 Model 5 TOYOTA PICKUP TOYOTA 1994 2004 FORD ESCAPE 2001 FORD F-150 2000 FORD TAURUS 1994 CHEVROLET S-10 GMC SIERRA 1500 GMC 1992 2000 HYUNDAI SONATA 1988 BUICK REGAL LINCOLN CONTINENTAL LINCOLN 1992 1995 FORD THUNDERBIRD <colgroup><col><col span="4"><col><col span="9"></colgroup><tbody> </tbody>

<tbody>
</tbody>

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