VBA Sorting function for numbers and text RRS feed

  • Question

  • I have a .txt file that contains both numbers and words each in different row, how do I make a VBA function that will sort all of the numbers in one column in excel and all of the words in an other column?

    I cannot figure this out and I can't find this on any forums.

    Thank you.

    Sunday, December 10, 2017 9:55 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Emi Zhang

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact

    Monday, December 11, 2017 1:50 AM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.

    Monday, December 11, 2017 3:18 AM
  • Hello ladna,

    You could read text string from the text line by line and the pick up the number to set to one column and the other part into another column. Here is the example code.

    Sub test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim strFilename As String
    strFilename = "C:\Users\Admin\Desktop\TestTxt.txt"
    Dim iFile As Integer: iFile = FreeFile
    Open strFilename For Input As #iFile
    ws.Cells(1, 1).Select
    Do Until EOF(1)
        Line Input #1, strTextLine
        i = 0
        i = i + 1
        Loop While IsNumeric(Left(strTextLine, i))
        Num = Trim(Left(strTextLine, i - 1))
        Application.ActiveCell.Value = Num
        Application.ActiveCell.NumberFormat = 0
        Application.ActiveCell.Offset(0, 1).Value = Trim(Right(strTextLine, Len(strTextLine) - Len(Num)))
        Application.ActiveCell.Offset(1, 0).Select
    Close #iFile
    End Sub

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    • Proposed as answer by Terry Xu - MSFT Wednesday, December 20, 2017 2:19 AM
    Tuesday, December 12, 2017 3:38 AM