Looking for a way to sort a range of cells that are text based that contain numbers.

[scam]

Hi all.
I am very limited with my excel VBA skill levels and would love some help to a problem I am facing.
I have a spreadsheet that is pulling allocated points for individuals from other spreadsheets.
Because the formula returns a text based result, when sorting the numbers the results are 1,10,11,12,2,20,21 etc.

Is there away to sort the numbers in the text only low to high? the code I have used is also below.

Please Login or Register  to view this content.

Any help would be greatly appreciated.

[Pauleyb]

You could create helper columns next to each one you wish to sort. In the helper column you would have a formula that parses out the number from the text in the column next to it (using MID() and FIND() functions). Your VBA would then select the helper column and the one next to it, but using the helper column as the one to sort. You could hide the helper column so that it does not appear on your main view.

[jindon]

scam,

Picture doesn't help at all.


Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Metaldrgn]

why not just add a second digit to them all if less than 100 (like 00, 01, 02)?

[scam]

Great suggestion for adding helper columns but the cells are being moved sometimes prior to sorting so helper cells would be incorrectly referencing blanks. I think I could make this work though.

[scam]

Like the second digit idea it should work. I think if I used the text function in the formula with 00 as placeholders, it should sort the way I want.

[scam]

why not just add a second digit to them all if less than 100 (like 00, 01, 02)?

So I added the function text(A1,"0000") into my formulas and instead of showing 1,2 3 etc now shows 0001,0002,0003 etc. Sorting works fantastic now. Thanks for the idea.