Separating Line of Data in different Cells

Hello Guys,

I was playing with RIGH, MID, LEFT and LEN to figure this out but it seams so complicated to me. I have 50+ Rows of data, all in one cell. Ex:

A1: Garden Grove $12.44 $17.65 70.5% $10.22 450
B1: Harbor City $12.44 $17.65 60.5% $10.22 460

I want to separate all values in this line in to individual cells.

Assuming the above line is in Cell A1, I need a formula to read A1 and put "Garden Grove" in A2, $12.44 in A3, $17.65 in A4, 70.5% in A5, $10.22 in A6, 450 in A7

Can anyone help me with it?

Perhaps something like this.

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Raw data		Location of seperators					Total
2			$	$		$		Length+1		Data 1	Data 2	Data 3	Data 4	Data 5	Data 6
3	Garden Grove $12.44 $17.65 70.5% $10.22 450		14	21	27	34	40	44		Garden Grove	12.44	17.65	70.5%	10.22	450
4	Harbor City $12.44 $17.65 60.5% $10.22 460		13	20	26	33	39	43		Harbor City	12.44	17.65	60.5%	10.22	460

C2:G2 are the seperators (the blank cells actually contain a space in there)
Formula in C3 (copied across to G3 and down)

Formula:

Please Login or Register  to view this content.

Formula in H3

Formula:

Please Login or Register  to view this content.

Data in J3 (copied across to O3 and down)

Formula:

Please Login or Register  to view this content.

Or see attached.

Thank you quekbc! I really Appreciate that.

Not a problem.

Just an additional note, you may have to add TRIM function to get rid of trailing spaces, and maybe VALUE function to convert them to numbers.

Also, if this resolves your initial query, can you kindly mark this thread as Solved by clicking on Thread Tools and subsequently Mark This Thread as Solved.

Thank you Marvin. I used a combo of both. All Formulas are correct now. You guys helped me BIG Time! Appreciate your fast response.