Sort Numerical-With-Dash Values In Order

Hey,

I'm having issues with sorting numbers in the following format in numerical order:

12345
12345-1
12345-2
12345-3
13456

They're all in Column A, but whether I format the cells as text or numbers, trying to sort them in order results in the following:

12345
13456
12345-1
12345-2
12345-3

What must I do to make this work?

Possibly add another column with a formula like:

=LEFT(A2,5) copied down

Then sort by that column first, and the original second.

NBVC, thank you, but I tried and failed to make your suggestion work - I don't quite know what you mean.

Dennis7849, that's what I've been having to do, but it gets annoying after a while.

Is there a code perhaps that would allow the sort function to work properly?

I mean, if for example your sort data is in column A starting at A2, then in another column you enter: =LEFT(A2,5) and copy down.. this extract the left 5 chars (assuming the code is consistently 5 chars before the dash or without dash)... then you sort the whole table, and sort first by that formula column, then by the original column A...

Okay, I see what you mean now - and that's cool, however, that's not going to work very well in my scenario..

Would you mind taking a look at my sample worksheet? The little sort button in Cell A1 is the one that I need to be able to sort with.

It won't sort the way you want using just that.... Why won't the above method work in your scenario?

Um, I just don't know how it would work. I don't want another column on that sheet. Could it be hidden? Could that sorting button somehow be linked to the second column and sort the A column accordingly?

You can hide the helper column, but you can't use the button in A1 to sort accordingly... you can however use the SORT in the DATA tab of the ribbon to sort by the hidden column first, then by column A.... or you can record/create a macro with a button on the sheet to do that sort at the click of a "SORT" button....

Ah, there we go then. I could just replace the little sort button thingy on Cell A1 with a sort button.

Any idea how to create a button that could do this all in one click then? I'm trying to learn how to do the whole button thing, but am still in Grade 0, I'm afraid. I'd be most grateful if you had the time to walk me through it.

I going offline for lunch, you can try recording a macro and assigning to a button.

If it doesn't work for you, I will try something after lunch...

I just got back from lunch myself. Okay, so is the second column necessary, or are we able to use the =LEFT(A*,5) formula right in the macro?

Ok, so I am definitely not the VBA writing champion ... I lean toward formula solutions only.... but I recorded a macro that does what Dennis suggested (to eliminate the formula need) and sort.... and then I tweaked the macro a bit to git rid of extraneous stuff...

Please Login or Register  to view this content.

You could probably clean it up a bit more.. but it does the job, I think quickly.

Haha, well you're definitely a VBA champion when compared to me.

Nice, that seems to work. Do you know of a way we can have the block of cells deselect after clicking the button?

You can add:

Please Login or Register  to view this content.

before the last application.screenupdate=True

Beautiful. That does the trick! Thank you very much, NBVC. You've been most helpful.