How to Sort Numerically prefixed alpha numeric rows by Number

Hello,

I have a macro that I am working on, and part of this macro sorts a group of project plans into several different cateogries. Then within these categories the project plans which contain a 5 digit numerical prefix project number and the name of the plan need to be sorted numerically in ascending order. I have tried this manually however it only sorts by alpha characters instead of the project numbers at the start of the alpha numeric string. I was wondering if anyone could give me any tips on a procedure I could use or how to go about solving this using vba. I have attached an example spreadsheet with tabs depicting the original format, manually sorted format, and the desired format.


example_Desired_Effect(1).xls

Here's one way. See the attached example. The macro below (and in the workbook)inserts a column in A, pulls in the numeric portion of your characters and sorts by that, then deletes the column. Hope this helps.

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

example_Desired.xls

Hi Jindon thanks, this over my head a little, however from what it looks like you are using the search function to check for a common attribute for each each to be seperated by. Now for my case this generic template will have these status texts uniquely driven, however I can use a "*" character to denote a common attribute to these sections. I have tried this however and it says, "No cells were found once it tries to populate the myAreas variable. If I am incorrect in understanding this would you be willing to let me know what the iserror and search functions do in this case? Thanks.

Delete the line of

Application.ScreenUpdating = False

Then step through the code.

You will see how it works.

Ok Thanks,

I see, that it does search for the text "Status" and my assumption there was correct, however it appears it does not accept special characters in the search function. Is there a way to use this solution with 4 unique seperators that do not have a common attribute such as "Status"

Can you upload a small sample?

I don't understand the meaning of "special characters".