How do i keep the spaces after using the: =SortWithinCell(A1,",",TRUE) vba? please.

[Adz847]

Hi,

I have used this vba to sort a list of names in a single cell alphabetically, however, when I do it removes all of the spaces... e.g

Darren H becomes darrenH...

Is there a way to keep the spaces please?

Any help greatly appreciated...

[dangelor]

Can you post the code for that function?

[Adz847]

Sure,

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String
CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "")
MyArray = Split(CelltoSortString, DelimitingCharacter)
For N = 0 To UBound(MyArray)
For M = 1 To UBound(MyArray)

If MyArray(M) < MyArray(M - 1) Then
TempValue = MyArray(M)
MyArray(M) = MyArray(M - 1)
MyArray(M - 1) = TempValue
End If

Next M
Next N
For N = 0 To UBound(MyArray)
SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter
Next N
SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1)
If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ")
End Function

Then back into the spreadsheet: =SortWithinCell(A1,",",TRUE) if the cell was A1 obviously.

Regards...

[dangelor]

Possibly...

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String
    MyArray = Split(CelltoSort.Value, DelimitingCharacter)
    For N = 0 To UBound(MyArray)
        For M = 1 To UBound(MyArray)
            
            If MyArray(M) < MyArray(M - 1) Then
                TempValue = MyArray(M)
                MyArray(M) = MyArray(M - 1)
                MyArray(M - 1) = TempValue
            End If
            
        Next M
    Next N
    For N = 0 To UBound(MyArray)
        SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter
    Next N
    SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1)
    If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ")
End Function

[Adz847]

Hi,

Thank you for this, however it is bringing up an error in the string:

Attachment 746342

Kind regards,

Adz

[dangelor]

Your attachment is not readable. Can you post the error and the highlighted line of code?