Sort Text In Cell Excel VBA Macro

In this post we see how to sort the text or words inside a single cell in the Excel worksheet using VBA macros. This may come handy when dealing with a bunch of text imported or scraped (also copy/pasted) into a single cell, which we may need to sort or handle in some way. The process consists of converting the text into an array that allows to count the number of words, sort the words in alphabetical order, and do other manipulations of the text such as extracting the words into a range of cells. The macro example below is used to sort the text inside a cell alphabetically. The instructions underneath explain how to perform other actions. Another option is to sort elements in the array itself - see this other post.

Macro/VBA code:

 

  Sub SortText()

      Dim cell_words As Variant, words_count As Integer

      Dim word As Variant, sorted_words As String, moveword As String

 

      cell_words = Split(ActiveCell, " ")

      'words_count = UBound(cell_words) + 1  'Optional word count

 

      Do

          moveword = ""

          For Each word In cell_words

              If InStr(sorted_words, word) = 0 Then

                  If moveword = "" Or UCase(word) < UCase(moveword) Then

                      moveword = word

                   End If             

              End If

          Next word

          sorted_words = sorted_words & moveword & " "

      Loop Until Len(sorted_words) > Len(ActiveCell)

 

      ActiveCell.Offset(0, 1).Value = Trim(sorted_words)

 

  End Sub

 

Macro explained:

• First, we declare variables to store the content in the cell as an array of words (cell_words), and each element or word in the array (word), both as Variants. The variable moveword stores temporarily the word that is moving its position alphabetically, while stored_words is the string where sorted words are added.
• We use the Split VBA function to get each of the words in the active cell separated by a space. This applies to scenario 1 in the picture above. The space can be replaced by any other character such as a comma, semi-colon, etc. In scenario 2, the words are placed vertically separated with a line break instead a space. In that case, we split the text by the line break, or more specifically, the line feed (vbLf).
• Additionally, we can count the number of words in the cell as the upper bound of the array created with Split plus one element (as lower bound if zero).
• Then we start a loop to go through each of the words, and a second nested loop that will check which word comes first in alphabetical order, and then add it to the sorted_words string. The following steps occur in the For loop:
• Prior to start the For loop, the moveword is set to null
• Check if the word has already been added to the sorted_words string, and if not then
• Check if the upper case of the word comes before the current word (if any) in alphabetical order. In that case, update the moveword.
• After the For loop, sorted_words is updated with the selected moveword (the first alphabetical position from the remaining words). Note that a space is added after the word. In case of scenario 2, a line break (vbCrLf) is added instead.
• The process continues until the length of the new sorted_words string equals the initial length in the active cell
• The result is added to the adjacent cell, but it can be added to the same cell removing the Offset reference, thus replacing the content with the sorted list.

This is how we sort text in a cell in Excel with VBA macros.

Other examples:

• How to Process HTTP response to Scrape Web Data with Excel VBA macros
  
• How to Send HTTP request to Scrape Web Data with Excel VBA macros
  
• How to Group Rows With Same Format with Excel VBA macros