Sorting text within a cell

[tpink]

I'm hoping there's a way to do this that's simpler than the brute-force method I'm doing now. I haven't come up with a good way to do a macro and while I have some minimal Visual Basic skills, I'm still trying to figure out how to use the editor in Excel and access modules from the workbook. So, I'm hoping someone else has needed to do something similiar so I can avoid reinventing the wheel.

One column of my spreadsheet contains cells that each contain a list of text phrases separated by commas. These phrases need to be alphabetized. (Example text: Nives Temprano, Nieves Temprano, Albillo Temprano, Acerba, Acerva, Besto Maduro, Blanco Ribera, Albillo de Cebreros) Since these aren't just single words to alphabetize, it complicates things. The alphabetized list of the example above would look like this: Acerba, Acerva, Albillo de Cebreros, Albillo Temprano, Besto Maduro, Blanco Ribera, Nieves Temprano, Nives Temprano. Working in Excel 2002 on Windows XP.

email at trpinkelton at ucdavis d.o.t edu

[VBA Noob]

Hi,

Select the column then select Data > Sort

VBA Noob

[tpink]

That's exactly what I don't want to do. It will sort by the first bit of each cell, rather than sorting all the phrases in the cell. Also, the rows are being sorted based on an entirely different column, which is working fine.

So the example before: "Nives Temprano, Nieves Temprano, Albillo Temprano, Acerba, Acerva, Besto Maduro, Blanco Ribera, Albillo de Cebreros" will sort to a row after say, this one "Agliano, Leatico, Livatica, Livaticia, Uva Liatica, Agliatico", because Nives comes after Agliano, but the text within the cells is still not sorted.

I've found some advice for sorting a space delimited set of single words within a cell, but I'm having trouble dealing with this one because it's phrases of indeterminite length. The commas should help somehow, but I'm running hard into the fact that my visual basic is beyond rusty. I haven't needed to go beyond the macro recorder in Excel for years.

[Bryan Hessey]

That's exactly what I don't want to do. It will sort by the first bit of each cell, rather than sorting all the phrases in the cell. Also, the rows are being sorted based on an entirely different column, which is working fine.

So the example before: "Nives Temprano, Nieves Temprano, Albillo Temprano, Acerba, Acerva, Besto Maduro, Blanco Ribera, Albillo de Cebreros" will sort to a row after say, this one "Agliano, Leatico, Livatica, Livaticia, Uva Liatica, Agliatico", because Nives comes after Agliano, but the text within the cells is still not sorted.

I've found some advice for sorting a space delimited set of single words within a cell, but I'm having trouble dealing with this one because it's phrases of indeterminite length. The commas should help somehow, but I'm running hard into the fact that my visual basic is beyond rusty. I haven't needed to go beyond the macro recorder in Excel for years.

You could set a macro to break it out 'Text to Columns' in a new sheet on 'comma' delimiter, sort, then re-join the data and replace the cell, (cell-by-cell down the column) but this would be better done with a little expertise in VB code.

---

[tpink]

You could set a macro to break it out 'Text to Columns' in a new sheet on 'comma' delimiter, sort, then re-join the data and replace the cell, (cell-by-cell down the column) but this would be better done with a little expertise in VB code.

---

Pretty much what I was doing. Not as slow as alphabetizing by hand, so to speak, but still pretty slow for 600+ rows.

Finally found this over on the Usenet Excel forums posted by JMB back in May. It's for a space delimited list, but I found the spots to replace space with comma. It is acting a bit odd in that it drops the first item from the list at the end of the sorted list, regardless of where it should end up. Odd, but that's pretty simple to clean up compared to the original problem.

Posting JMB's function below for anyone else who searches this forum on the topic.

******
Function MySort(rngCell As Range) As String
Dim arrData As Variant
Dim i As Long
Dim t As Long
Dim strTemp As String

arrData = Split(rngCell.Value, " ", -1, vbTextCompare)

For i = LBound(arrData) To UBound(arrData) - 1
For t = i + 1 To UBound(arrData)
If LCase(CStr(arrData(t))) < LCase(CStr(arrData(i))) Then
strTemp = arrData(i)
arrData(i) = arrData(t)
arrData(t) = strTemp
End If
Next t
Next i

MySort = Join(arrData, " ")

End Function

*************

[Bryan Hessey]

Pretty much what I was doing. Not as slow as alphabetizing by hand, so to speak, but still pretty slow for 600+ rows.

Finally found this over on the Usenet Excel forums posted by JMB back in May. It's for a space delimited list, but I found the spots to replace space with comma. It is acting a bit odd in that it drops the first item from the list at the end of the sorted list, regardless of where it should end up. Odd, but that's pretty simple to clean up compared to the original problem.

Posting JMB's function below for anyone else who searches this forum on the topic.

******
Function MySort(rngCell As Range) As String
Dim arrData As Variant
Dim i As Long
Dim t As Long
Dim strTemp As String

arrData = Split(rngCell.Value, " ", -1, vbTextCompare)

For i = LBound(arrData) To UBound(arrData) - 1
For t = i + 1 To UBound(arrData)
If LCase(CStr(arrData(t))) < LCase(CStr(arrData(i))) Then
strTemp = arrData(i)
arrData(i) = arrData(t)
arrData(t) = strTemp
End If
Next t
Next i

MySort = Join(arrData, " ")

End Function

*************

Great to see, I thinkl I'll replace the loop I was testing with that for future reference.

Cheers
---

[oldchippy]

Pretty much what I was doing. Not as slow as alphabetizing by hand, so to speak, but still pretty slow for 600+ rows.

Finally found this over on the Usenet Excel forums posted by JMB back in May. It's for a space delimited list, but I found the spots to replace space with comma. It is acting a bit odd in that it drops the first item from the list at the end of the sorted list, regardless of where it should end up. Odd, but that's pretty simple to clean up compared to the original problem.

Posting JMB's function below for anyone else who searches this forum on the topic.

******
Function MySort(rngCell As Range) As String
Dim arrData As Variant
Dim i As Long
Dim t As Long
Dim strTemp As String

arrData = Split(rngCell.Value, " ", -1, vbTextCompare)

For i = LBound(arrData) To UBound(arrData) - 1
For t = i + 1 To UBound(arrData)
If LCase(CStr(arrData(t))) < LCase(CStr(arrData(i))) Then
strTemp = arrData(i)
arrData(i) = arrData(t)
arrData(t) = strTemp
End If
Next t
Next i

MySort = Join(arrData, " ")

End Function

*************

Hi tpink,

I've just tried this out, but it breaks it down by space, this was the result I got

Acerba, Acerva, Albillo Albillo Besto Blanco Cebreros de Maduro, Nieves Nives Ribera, Temprano, Temprano, Temprano,

It needs a little bit of modification to give the result you want

PS - Just got it, the split and the join needs to be ", "

[tpink]

Hi tpink,

I've just tried this out, but it breaks it down by space, this was the result I got

Acerba, Acerva, Albillo Albillo Besto Blanco Cebreros de Maduro, Nieves Nives Ribera, Temprano, Temprano, Temprano,

It needs a little bit of modification to give the result you want

PS - Just got it, the split and the join needs to be ", "

Yes, I posted the original code, rather than my modification for the commas. Sorry, I wasn't clear about that.