MS Excel putting numbers in order.

[ben10]

can someone give me a formula for excel to put these numbers in order when I paste them onto excel.

example from this:

2
195
67
101
47
80 x 2
194
40
7 x 3
34 x 2
85 x 3

result would be:

2
7 x 3
34 x 2
40
47
67
80 x 2
85 x 3
101
194
195

thanks.

[lsemmens]

Convert them all to text then your sort should work. I'm assuming those formulae are to remain as such and not the calculated result. Help in Excel is generally very good for simple tasks like this.

[ben10]

Convert them all to text then your sort should work. I'm assuming those formulae are to remain as such and not the calculated result. Help in Excel is generally very good for simple tasks like this.

thanks for the quick reply.
i just did that. covert whole column to text. then sort it. and the result is not what i wanted.
2
40
47
67
101
194
195
34 x 2
7 x 3
80 x 2
85 x 3

[jglnb]

Here is an idea :

1. Format all the cells as text (not really needed but it makes the data look "nicer" (all left justified)
2. In the next column use the find function to search for a space in the cells as sorted eg "=+FIND(" ",B7)"
3. Now, in the next column over, use =+IF(ISERR(C7),VALUE(B7),VALUE(LEFT(B7,C7))) to either get the value of the initial field when there is no space or get the value of the number up to the space
4. Now sort the numbers by the column calculated in step 3

Be careful that if your list has 2 data pieces which are the same up to the space, the results may not be perfect (eg 80 x 5 and 80 x 6 would not necessarily be sorted correctly) so you may have to do a sort on the original column as the "secondary" sorting value to get some predictable values.

Start Data Location of Space Calc Result
2 =+FIND(" ",B7) =+IF(ISERR(C7),VALUE(B7),VALUE(LEFT(B7,C7)))
195 =+FIND(" ",B8) =+IF(ISERR(C8),VALUE(B8),VALUE(LEFT(B8,C8)))
67 =+FIND(" ",B9) =+IF(ISERR(C9),VALUE(B9),VALUE(LEFT(B9,C9)))
101 =+FIND(" ",B10) =+IF(ISERR(C10),VALUE(B10),VALUE(LEFT(B10,C10)))
47 =+FIND(" ",B11) =+IF(ISERR(C11),VALUE(B11),VALUE(LEFT(B11,C11)))
80 x 2 =+FIND(" ",B12) =+IF(ISERR(C12),VALUE(B12),VALUE(LEFT(B12,C12)))
194 =+FIND(" ",B13) =+IF(ISERR(C13),VALUE(B13),VALUE(LEFT(B13,C13)))
40 =+FIND(" ",B14) =+IF(ISERR(C14),VALUE(B14),VALUE(LEFT(B14,C14)))
7 x 3 =+FIND(" ",B15) =+IF(ISERR(C15),VALUE(B15),VALUE(LEFT(B15,C15)))
34 x 2 =+FIND(" ",B16) =+IF(ISERR(C16),VALUE(B16),VALUE(LEFT(B16,C16)))
85 x 3 =+FIND(" ",B17) =+IF(ISERR(C17),VALUE(B17),VALUE(LEFT(B17,C17)))

Clear as mud ???

Hope this helps,

Cheers,
JG

[Philquad]

just put it in 2 columns
miss the x
select it all, sort by custom, 1st row

2
7 3
34 2
40
47
67
80 2
85 3
101
194
195

or even 3 columns, put x in the middle one