I think the Subject says it all. I have a list of items and they
start with M. So they read M1, M2, M3 ...Etc. When you sort this it
puts all the M1s together, like M1, M10, M100. Is there a way in
excel to have it skip the first character and sort by just the
numbers? If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay

hi
custom sorts are usually done with a helper column.
in a blank column to the right of your data, enter this formula.....
assuming a header row....
=right(A2,len(a2)-1)

copy down as far as needed.
sort with the helper column as primary sort.

regards
FSt1
"jlclyde" wrote:

I think the Subject says it all. I have a list of items and they
start with M. So they read M1, M2, M3 ...Etc. When you sort this it
puts all the M1s together, like M1, M10, M100. Is there a way in
excel to have it skip the first character and sort by just the
numbers? If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay

In an adjacent column =RIGHT(A1,LEN(A1)-1)

Double-click on the cell to fill down.

Sort on that column.

If happy, delete the column.


Gord Dibben MS Excel MVP


On Wed, 21 Jan 2009 13:54:32 -0800 (PST), jlclyde
wrote:

I think the Subject says it all. I have a list of items and they
start with M. So they read M1, M2, M3 ...Etc. When you sort this it
puts all the M1s together, like M1, M10, M100. Is there a way in
excel to have it skip the first character and sort by just the
numbers? If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay

Note: when you are sorting you will be asked what to do with text that look
like numbers.

Select treat as numbers.


Gord

On Wed, 21 Jan 2009 14:51:33 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

In an adjacent column =RIGHT(A1,LEN(A1)-1)

Double-click on the cell to fill down.

Sort on that column.

If happy, delete the column.


Gord Dibben MS Excel MVP


On Wed, 21 Jan 2009 13:54:32 -0800 (PST), jlclyde
wrote:

I think the Subject says it all. I have a list of items and they
start with M. So they read M1, M2, M3 ...Etc. When you sort this it
puts all the M1s together, like M1, M10, M100. Is there a way in
excel to have it skip the first character and sort by just the
numbers? If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay

On Jan 21, 4:23*pm, FSt1 wrote:
hi
custom sorts are usually done with a helper column.
in a blank column to the right of your data, enter this formula.....
assuming a header row....
=right(A2,len(a2)-1)

copy down as far as needed.
sort with the helper column as primary sort.

regards
FSt1



"jlclyde" wrote:
I think the Subject says it all. *I have a list of items and they
start with M. *So they read M1, M2, M3 ...Etc. *When you sort this it
puts all the M1s together, like M1, M10, M100. *Is there a way in
excel to have it skip the first character and sort by just the
numbers? *If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

i am thinking that I will just do a macro to do text to columns. Then
sort, then put the cells back together. should be pretty simple. I
was just hoping for another way to do this.

Thanks,
Jay

hi
a helper column would be simplier.

my thoughts
Regards
FSt1

"jlclyde" wrote:

On Jan 21, 4:23 pm, FSt1 wrote:
hi
custom sorts are usually done with a helper column.
in a blank column to the right of your data, enter this formula.....
assuming a header row....
=right(A2,len(a2)-1)

copy down as far as needed.
sort with the helper column as primary sort.

regards
FSt1



"jlclyde" wrote:
I think the Subject says it all. I have a list of items and they
start with M. So they read M1, M2, M3 ...Etc. When you sort this it
puts all the M1s together, like M1, M10, M100. Is there a way in
excel to have it skip the first character and sort by just the
numbers? If there is not, then I will have to split it into seperate
columns, sort, and then put them back together.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

i am thinking that I will just do a macro to do text to columns. Then
sort, then put the cells back together. should be pretty simple. I
was just hoping for another way to do this.

Thanks,
Jay