• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting numbers that look like text

C

cubs610

Member
Hi Everyone,

The other day this question came across my desk, and even though I was able to create a plausible solution I can't help but to think there might be a better (more efficient) way, so I figured asking the collective might help. Here is the situation.

Imagine a column that lists a bunch of dimensions (think measurements).

For example:

2 X 4
12 x 9
11 X 7
3 x 4
1 x 2
2 X 1
2 X 2
10 x 2
4 x 4
5 X 3 (etc)

When you go to sort the column excel lists the 1's first, then 2's, 3's as you are aware. The challenge is to sort the values in sequential order (1,2,3,4, 5, an so on).

One easy solution would be to create a helper column(s) and use the text to column feature. The other would be to create a formula the blends the VALUE, LEN and FIND functions that returns just the first number into a helper column, from which you can sort the new helper column.

That said, something in the back of my mind thinks there is an easier way. Oh and before I forger, although a custom list was a thought the total amount of dimensions in the column is over 100, so typing the list order might be a pain. :)

Love to hear your thoughts.

-Dave
 
But if you have more than one 1s or 2s ectera then you will have more say 1' or 2s then threes or fours so you will have:-
1
12
13
21
22
3
4
5
6
7
71
72
8
9
91

If you did as you are after
1
2
3
4
ectera

What are you going to do with the orphan numbers

1
1
2
2
ectera
 
bobhc said:
But if you have more than one 1s or 2s ectera then you will have more say 1' or 2s then threes or fours so you will have:-
1
12
13
21
22
3
4
5
6
7
71
72
8
9
91

If you did as you are after
1
2
3
4
ectera

What are you going to do with the orphan numbers

1
1
2
2
ectera
Click to expand...


Hi Bob,

Sure. Please allow me to clarify. With the helper column we just needed the first number. Anything after the X was discounted/ not relevant on the grand scheme. So for example when the formula was used it just returned the first number in the measurement of which we just sorted by it since that was the desired sort order. Same with the text to column. The text to column gave us two new columns, of which we deleted the second number (info to the right of the 'x') since it wasn't necessary.

Hope this helps.
 
Maybe........

1] Assume your data in A2:A11

2] B2, formula copy down :

=IFERROR(SUBSTITUTE(SMALL(MMULT(0+SUBSTITUTE(SUBSTITUTE(A$2:A$11," x ",".")," X ","."),1),ROW(A1)),"."," x "),"")

Regards
 
You must log in or register to reply here.
Back
Top