sort text numbers as number

  • Thread starter Thread starter JACOB
  • Start date Start date
J

JACOB

I have a list like this
1-apple
2-orange
....
11-pears
12-grapes..
Now when I sort it, it will sort number 2 after number 12 because it takes
it as text, how can I have Excel sort it by the numeric value of the
begining of the filed?
 
Jacob,

You can make a helper column, and sort on that column.

=LEFT(A2,SEARCH("-",A2)-1)

This should yield the number part at the beginning of the cell - it looks
for a hyphen. It will fail if it doesn't find a hyphen; the formula can be
expanded to behave more nicely if that's a possibility.

You can hide the helper column, but it may not be possible to use the sort
buttons on the toolbar (they're a bit dangerous anyway). Works fine with
Data - Sort.

Regards from Virginia Beach,

EarlK
 
I would suggest storing different data types in different
fields so you don't have this problem - use the
spreadsheet as it is designed to be used in other words.
You'd have a column labled 'Number' with the number of
thisngs, and another column next to it labeled "Fruit'
which specifies what kind of fruit. Now you can sort buy
numbers or by which kind of fruit. To convert your
existing data you could select the column in question and
split it into numeric and text columns using the
Data...Text to Columns feature. Make a backup copy of
your spreadsheet and give it a try. This will of course
change the number of rows in your data sheet which might
cause problems depending on what functions are coded to do
whatever else to with the data. Let me know if you need
more help....
 
JACOB said:
I have a list like this
1-apple
2-orange
...
11-pears
12-grapes..
Now when I sort it, it will sort number 2 after number 12 because it takes
it as text, how can I have Excel sort it by the numeric value of the
begining of the filed?
Click to expand...

If your first item is Cell A1,put this formula in B1, then copy down
to the last row in Column A
=VALUE(LEFT(A1,FIND("-",A1)-1))
Then you can sort by Column B
 
Back
Top