weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

9tanstaafl9 · May 7, 2013

Hi. I have single column of text that looks mostly like numbers, and I need it to sort (using VBA) in a more logical looking way. For example I would like it to look something like this:

1
2
2.3
2.3a
3
100
100-4321
--------------------
Not 1, 100, 2…
----------------------

I NEED TO FIX THIS USING VBA IF AT ALL POSSIBLE – This data is originally gathered by a query on another sheet (and comes in the order I’d like I might add). The problem is that I then copy with my macro onto the main sheet, and a that point it needs sorted with the old data that was there the week before. That’s when the sort looks weird.

Unfortunately I’m dealing with internal company codes that can’t be changed.

Is this a doable thing or not? If you say no I can tell them too bad and they have to deal with the ugly sort, but I don’t want to make that claim and then look like an idiot. More of an idiot – I don’t deal well with people. J

Thanks in advance for any help!

Using Excel 2010.

Sorry if this was already covered, but the search feature on MrExcel.com keeps bombing on me and I’m not finding what I need through Google.

9tanstaafl9 · May 16, 2013

Rick Rothstein said:
If you have more than one column involved in the sorting operation, then show me sample data from both columns, then show me how that data should look after being sorted. Try to use a varied data sample similar to the variation I showed in Message #14, but for the two columns involved in determining the final sorted order.

Hi again. I am NOT trying to rush you, but rather to check to see if I ticked you off so much with my earlier screw up that you don't want to help me any more (which would be completely understandable, but in that case I have to start trying to figure this out myself). I will gladly wait patiently if I know I'm still on your radar.

Thank you!

Jennifer

9tanstaafl9 · May 20, 2013

NEVERMIND - Solved it! There is an option you can add at the end of the sort code, DataOption:= xlSortTextAsNumbers that will work well enough. I was unaware this was an option. Apparently when not using macros, and doing a normal sort, this window pops up automatically when it detects you have number and text. We only just started using a program where this could happen, and I had never had the issue come up before and so hadn't known of its existence. I had been sorting within the macro and so never saw that option. I apologize sincerely because this is such a simple thing and I should have been aware of it. I did not mean to waste anyone's time.

weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Excel Facts

9tanstaafl9

Well-known Member

Similar threads

Forum statistics

Share this page

weird Alphanumeric sorting issue, need to fix in VBA, other searches no help

9tanstaafl9

Well-known Member

9tanstaafl9

Well-known Member

Excel Facts

9tanstaafl9

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock