Using excel.application in vbscript, how do I sort text as numbers?

I am able to sort multiple columns. But I am not able to sort text as numbers. That is, both 4-digit and 3-digit numbers (as text) are in ascending order but the former comes before the latter. What should my sort method look like in vbscript?
jgsimbulanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NicksonKohConnect With a Mentor Commented:
I believe you have to convert the text numbers to real numbers. For that you can use Text To Column.

Attach is sample code which is generated from macro recording.
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
    'After conversion, you can sort as follows
    Range("A1").Select
    Range("A1:A7").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Open in new window

0
 
StellanRosengrenConnect With a Mentor Commented:
Hi,
just remember that you cannot set parameters by name in vbscript. You have to put them in the correct position.
For example, Range("A:A").TextToColumns Range("A1"),1,1,0,1,,,,,,,,,1
Also, you cannot use the named constants like xlDelimited, xlDoubleQuote etc.
You have to use the number. For example, 1 instead of xlDelimited.

Kind regards,
Stellan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.