Click here to Skip to main content
15,896,330 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have a spread sheet that has 'n' columns and 'n' rows. In every cell having more than one values, i want to sort them in alphabetically.

If i tried to sort the particular cell means that will not work properly

And then if i select the cell and (it has more than one items) copy the items,

and then paste in new spreadsheet means every items of the particular cell sorted but put the every items in the different cells not in single cell.

in spreadsheet,i want to sort each cell alphabetically
Posted
Comments
Wendelius 1-Aug-14 8:45am    
The question isn't quite clear. You mean each cell has multiple values? Can you post an example of the data and an example what you would like to achieve.

1 solution

You will have to do this using a VBA macro.

For each cell in your worksheet, split the contents as individual items into an array, sort the array, then update the cell with the new data.

This function works
VB
Public Sub SortACell(aCell As Range, sep As String)

    'Split the cell into its individual "values"
    Dim S1() As String
    S1 = Split(aCell.Value2, sep)

    'Sort the array now containing the cell contents
    'See http://www.cpearson.com/excel/SortingArrays.aspx
    Dim b As Boolean
    b = QSortInPlace(S1)

    'Rebuild the cell contents
    Dim sorted As String
    Dim s2 As Variant
    For Each s2 In S1
        sorted = sorted & s2 & sep
    Next

    'Remove last chr$(10)
    sorted = Left$(sorted, Len(sorted) - 1)

    'Update the cell with the sorted string
    aCell.Value2 = sorted
End Sub
I haven't reproduced the sorting functions here - you can find a good example at http://www.cpearson.com/excel/SortingArrays.aspx[^]
To call the code do something like this:
VB
Range("A1").Select
Dim c As Range
For Each c In ActiveCell.CurrentRegion.Cells
    If Trim(c.Value2) <> "" Then
        SortACell c, Chr$(10)
    End If
Next

Note - I've assumed that the data in your cells contains linefeeds to separate the values (because when you paste the contents they go into separate cells) - that's the chr$(10) as the separator
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900