split values in different cells, lot of variables

[johnnyflex]

Hello to all!

I have a list of values in one cell.

The case :

5.1-5.28, 5.1-5.28, 9.5-10, 9.5-10

I want to extract the first value in one cell, second in another, third... you got it.

But sometimes I can have let's say :

5.1, 5.1-5.28,10, 9.5-10

In this case, the first and third value are "alone" between commas, without a "-" and another value before the comma.
For this set of data, I need to put :

The first number in the first cell
The second in the third cell
The third in the fourth cell
The fourth in the fifth cell
The fifth in the seventh cell
the sixth in the eight cell

Long story short :

If I have 2 value separated by a dash, they go in adjacent cells.
If I have only one, then I skip a cell.

I'm not sure I'm clear, but I tried multiple LEFT, MID, SUBSTITUTE without result.
I know how to do it for the first and last set of data in my cells but I'm stuck for the middle.

Any help will be greatly appreciated.

Thank you

 

[Rick Rothstein]

Assuming your values are located in Column A with the first value in cell A1, put this formula in cell B1 and copy it across for as many columns as you think you will ever have numbers to fill them in with, then copy all of those cells down to the bottom of your data...

[table="width: 500"]
[tr]
	[td]=TRIM(MID(SUBSTITUTE("-"&TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",200)),1+INT((COLUMNS($B:B)-1)/2)*200,200))&"-","-",REPT(" ",200)),(2-MOD(COLUMNS($B:B),2))*200,200))[/td]
[/tr]
[/table]

 

[johnnyflex]

Rick,

How to say it! You are a Jedi master!

thank you so much!

Could you explain more how did you build this awesome formula?

Thanks

Jon