Sorting

  • Thread starter Thread starter Rob E
  • Start date Start date
R

Rob E

I have a list of numbers in a column within my data list:

300
301M
302
303
304M

Is there any way that I can sort my data list by this
column but get Excel to ignore the 'm's, as I want it
sorted numerically?

Any help would be really appreciated.

Thanks,

Rob E.
 
Rob

I reckon you'll have to use a helper column, something like this:
=IF(RIGHT(B8,1)="M",VALUE(LEFT(B8,LEN(B8)-1)),B8)


Andy.
 
Rob E said:
I have a list of numbers in a column within my data list:

300
301M
302
303
304M

Is there any way that I can sort my data list by this
column but get Excel to ignore the 'm's, as I want it
sorted numerically?

Any help would be really appreciated.

Thanks,

Rob E.
Click to expand...

With respect, you don't have a list of numbers. 301M is a text string, not a
number. (I'm sure you know this really, and I'm sorry if it sounds pedantic,
but accuracy is essential in solving this sort of problem.) Your 300 may be
a number or text, depending on cell formatting and how it was entered.

Will M be the only possible suffix? If so, insert a column alongside your
list, which I will assume is column A. In the first cell of this new column,
put the formula
=VALUE(SUBSTITUTE(A1,"M",""))
and copy down as far as necessary.
Then you can sort on this new column.
 
Back
Top