Sort alphanumerically (updated)

I'm using Excel Mac 2011 first off. I've uploaded the file.

I put this text into a separate excel document but I'm basically taking all of this information and inserting it into another excel document and sorting alphabetically. I have no problem doing so except with this list. It's for my sports card collection and the card number is 52 and then it breaks off into a sub category. If you take the information and sort like I would typically it puts it into a list like this (see below) instead of (see below). Please help! Also if you're explaining it to me can you please make sure the equation is simplified I'm a novice.

I also lost grid lines on another sheet I have, how do I get them back? I'm aware of the 'gridlines' option however it doesn't make them reappear. Thanks for the help!sheffield moments and milestones.xlsx


This is the list that I'm importing the information from "moment and milestones" into. I'm inserting it in the end of row A. I highlight the last selection of R all the way up to A 2 and then sort on value. It's worked fine until now. I have been trying things, I just tried the delimiter however I couldn't get it to sort still. I managed to make another row in order however it wouldn't sort on that. Thanks
Sheffield Collection.xlsx


Incorrect
2008 Topps Moments and Milestones #52-1
2008 Topps Moments and Milestones #52-10
2008 Topps Moments and Milestones #52-100
2008 Topps Moments and Milestones #52-101
2008 Topps Moments and Milestones #52-102
2008 Topps Moments and Milestones #52-103
2008 Topps Moments and Milestones #52-104
2008 Topps Moments and Milestones #52-105
2008 Topps Moments and Milestones #52-106
2008 Topps Moments and Milestones #52-107
2008 Topps Moments and Milestones #52-108
2008 Topps Moments and Milestones #52-109
2008 Topps Moments and Milestones #52-11
2008 Topps Moments and Milestones #52-110
2008 Topps Moments and Milestones #52-111
2008 Topps Moments and Milestones #52-112
2008 Topps Moments and Milestones #52-113
2008 Topps Moments and Milestones #52-114
2008 Topps Moments and Milestones #52-115
2008 Topps Moments and Milestones #52-116
2008 Topps Moments and Milestones #52-117
2008 Topps Moments and Milestones #52-118
2008 Topps Moments and Milestones #52-119
2008 Topps Moments and Milestones #52-12
2008 Topps Moments and Milestones #52-120
2008 Topps Moments and Milestones #52-121
2008 Topps Moments and Milestones #52-122
2008 Topps Moments and Milestones #52-123
2008 Topps Moments and Milestones #52-124
2008 Topps Moments and Milestones #52-125


Correct
2008 Topps Moments and Milestones #52-2
2008 Topps Moments and Milestones #52-3
2008 Topps Moments and Milestones #52-4
2008 Topps Moments and Milestones #52-5
2008 Topps Moments and Milestones #52-6
2008 Topps Moments and Milestones #52-7
2008 Topps Moments and Milestones #52-8
2008 Topps Moments and Milestones #52-9
2008 Topps Moments and Milestones #52-10
2008 Topps Moments and Milestones #52-11
2008 Topps Moments and Milestones #52-12
2008 Topps Moments and Milestones #52-13
2008 Topps Moments and Milestones #52-14
2008 Topps Moments and Milestones #52-15
2008 Topps Moments and Milestones #52-16
2008 Topps Moments and Milestones #52-17
2008 Topps Moments and Milestones #52-18
2008 Topps Moments and Milestones #52-19
2008 Topps Moments and Milestones #52-20
2008 Topps Moments and Milestones #52-21
2008 Topps Moments and Milestones #52-22
2008 Topps Moments and Milestones #52-23
2008 Topps Moments and Milestones #52-24
2008 Topps Moments and Milestones #52-25
2008 Topps Moments and Milestones #52-26
2008 Topps Moments and Milestones #52-27
2008 Topps Moments and Milestones #52-28
2008 Topps Moments and Milestones #52-29
2008 Topps Moments and Milestones #52-30
2008 Topps Moments and Milestones #52-31

Split: 2008 Topps Moments and Milestones #52-1 in two columns
Column A: 2008 Topps Moments and Milestones #52-
column B: 1

Or change 1 in 001

Hi,

You could add a helper column: for example, assuming your list starts in cell A2, enter this formula in cell B2 and copy down:

=--SUBSTITUTE(MID(A2,FIND("#",A2)+1,255),"-","")

You can then sort on this column to get your desired result.

Sure there's a more efficient method, though.

Regards

Use Data | Text to Columns | Delimited. Choose minus/hyphen as the delimiter. Set the destination to the next column ... AND DO NOT IMPORT THE FIRST COLUMN.

That should get the numbers in a separate column. Sort on that. Then hide it, or delete it, as you choose.


Regards, TMS

2008 Topps Moments and Milestones #52-1
2008 Topps Moments and Milestones #52-1
2008 Topps Moments and Milestones #52-10
2008 Topps Moments and Milestones #52-100

2008 Topps Moments and Milestones #52-109
2008 Topps Moments and Milestones #52-11
2008 Topps Moments and Milestones #52-110

2008 Topps Moments and Milestones #52-119
2008 Topps Moments and Milestones #52-12
2008 Topps Moments and Milestones #52-120

Change above in:
2008 Topps Moments and Milestones #52-001
2008 Topps Moments and Milestones #52-010
2008 Topps Moments and Milestones #52-100

2008 Topps Moments and Milestones #52-109
2008 Topps Moments and Milestones #52-011
2008 Topps Moments and Milestones #52-110

2008 Topps Moments and Milestones #52-119
2008 Topps Moments and Milestones #52-012
2008 Topps Moments and Milestones #52-120

@TMShucks

"Use Data | Text to Columns | Delimited. Choose minus/hyphen as the delimiter."

But what if there are #53, #54,...,etc. entries as well as those #52 listed?

Regards

Originally Posted by TJCloutier

I also lost grid lines on another sheet I have, how do I get them back? I'm aware of the 'gridlines' option however it doesn't make them reappear.

Check if the cells are filled with a colour (even white), because that causes the gridlines to disappear. Another thing could be that the cells have been given a white border line.
So select No Filling Colour and No Borders

If you're adamant that you're not willing to use an extra column (or columns), nor to go through any intermediate steps, then I don't see how this can be achieved (without perhaps VBA).

Regards

well as said you need a helper column
insert a column temporarily
say b
then in b1
=LEFT(A1,FIND("-",A1))&TEXT(MID(A1,FIND("-",A1)+1,5),"000")
fill down ,sort everything by column b delete column b

This is going to sound a "little off the wall" but it works with your example.

Copy the data into Word. Select the data and convert to table. Sort the table on column 6. When doing the sort, select column 6 to be NUMBER and sort order to be DESCENDING. That's right DESCENDING. The order will be correct. Convert the table back to text and copy and paste back into Excel. When converting back to text, select OTHER and enter a SPACE in the box for the "other" character.

"To be honest I don't understand why I can't just input a simple formula or have an option to have this happen"

To be fair, you've been offered at least two formula-based solutions, though, since you continue to insist on not using an extra column, I'm not sure exactly where you expect these 'simple' formulae to go.

Regards

This has me baffled as to why You can't use a helper column and sort on that.

Here's another somewhat unorthodox way of getting what you appear to need.

In say E1, Drag/Fill Down

Formula:

Please Login or Register  to view this content.

Then if required in F1, drag across then down

Formula:

Please Login or Register  to view this content.

Copy and Paste Special > Values the result to your destination

However I really can't see the point in this, what aren't you telling us?
For instance are all the strings in front of the "-" separator the same in your real list?

see attached

it tells you what to do,sort by column b

have you the whole list?