Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1097
  • Last Modified:

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?
0
jgsimbulan
Asked:
jgsimbulan
2 Solutions
 
NicksonKohCommented:
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
 
StellanRosengrenCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now