Separate number into their own cells.

[gleemonex69]

Good morning, Excel gods. I was wondering if there was some sort of formula to separate data into their own cells for example:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jul-10[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]2.5 to 3.5[/TD]
[/TR]
[TR]
[TD]Poultry[/TD]
[TD]2.0 to 3.0[/TD]
[/TR]
</tbody>[/TABLE]

Could I somehow with a formula do something like this?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jul-10[/TD]
[TD]Jul-10[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]Meats[/TD]
[TD]2.5[/TD]
[TD]3.5[/TD]
[TD]3.0[/TD]
[/TR]
[TR]
[TD]Poultry[/TD]
[TD]2.0[/TD]
[TD]3.0[/TD]
[TD]2.5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much for your assistance.

 

[tyija1995]

Hey,

try these formulae:

C2:
=VALUE(LEFT(B2,SEARCH("to",B2)-2))
D2:
=VALUE(MID(B2,SEARCH("to",B2)+3,LEN(B2)))
E2:
=AVERAGE(C2:D2)

This assumes that each set of numbers is split by the word "to" and that B2 is the text containing "2.5 to 3.5" etc.

 

[gleemonex69]

You sir, are a life saver. Thank you so much for your help.