Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Is it possible to get Word or Excel 2007 to sort the contents of multiple cells in a table row or column, cell by cell rather than the row or column as a whole?

    For example, if a column has two cells, A1 containing "Dog, Cat" and A2 containing "Banana, Apple", the result with an ascending alphabetical sort should be "Cat, Dog" in A1 and "Apple, Banana" in A2.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    If I read your question properly you are trying to sort within a cell? It this is correct I know of no way to make that happen in either Word or Excel w/o writing some VBA code to parse the cell contents into an array, sort that array, then reassemble the values into a single string and then store it back into the cell.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Murgatroyd,

    Here's one way to sort a string of words, separated by commas, in cell A1 in the first Word table in a document:
    Code:
    Sub Demo()
    Application.ScreenUpdating = False
    Dim tRng As Range, i As Integer, strTxt As String
    With ActiveDocument.Tables(1)
      Set tRng = .Cell(1, 1).Range
      tRng.End = tRng.End - 1
      With tRng
        strTxt = .Text
        i = UBound(Split(strTxt, ","))
        .ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=1, NumRows:=i, Format:=wdTableFormatNone
        .Sort ExcludeHeader:=False, FieldNumber:="Column 1", SortFieldType:=wdSortFieldAlphanumeric, _
          SortOrder:=wdSortOrderAscending, CaseSensitive:=False
        .Rows.ConvertToText Separator:=wdSeparateByParagraphs, NestedTables:=True
        strTxt = Replace(.Text, vbCr, ", ")
        .Text = Left(strTxt, Len(strTxt) - 2)
      End With
    End With
    Set tRng = Nothing
    Application.ScreenUpdating = True
    End Sub
    With a few tweaks, the code could be made to work with any table or cell and with other word separators instead of commas.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I was hoping that there might be a way to select a range of cells and sort their contents in one step.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Murgatroyd,

    As I said in my previous post:
    With a few tweaks, the code could be made to work with any table or cell
    That applies equally to a set of selected cells.

    Here's a challenge for you:
    Have a go at modifying the code to do what you want; there are plenty of examples here on how to loop through a collection, so it's just a matter of adapting one of those to my code, or vice-versa. If you get stuck, post back with your code and we'll give you a hand.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •