Separate Words and Numbers In Cell Excel VBA Macro

In this post we learn to separate words and numbers within cells in Excel using VBA macros. The macro splits the value in a cell into parts separated by a space (parts can be letters, words, numbers, etc). We can easily adapt the macro to separate the value by other character (a comma, a semi-colon, etc). In this particular example, we will get the keyword in that cell in one column, a number (if any) in another column, and a date (if any) in a third column.  The keyword can be based on a reference value or list of values, but in this case, we will just get the longest word in the sentence, assuming that’s the keyword in that sentence. If there is more than one number (or more than one date), the macro gets the last one. That can be easily changed as needed to get a number or date in a particular position.

Macro/VBA code:

 

  Sub SeparateWordsNumbers()

 

      Dim r As Integer, sentence As String, part_length As Integer

      Dim sentence_parts As Variant, part As Variant

 

      For r = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

          sentence = Range("A" & r).Value

          sentence_parts = Split(sentence, " ")

          part_length = 0

 

          For Each part In sentence_parts

              If IsDate(part) = True Then

                  Range("D" & r).Value = part

              ElseIf IsNumeric(part) = True Then

                  Range("C" & r).Value = part

              Else

                  If Len(part) >= part_length Then

                      Range("B" & r).Value = part

                      part_length = Len(part)

                  End If

              End If

          Next part

   

      Next r

 

  End Sub

 

Macro explained:

• We declare a numerical variable (r) to store the row number while looping and a string to store the sentence in the source cell. We also have variables to store the array of parts in the sentence and each individual part (those are Variants).
• Then we start a loop through each row with data (skipping the header) within the used range in the active worksheet.
• Within the loop, we get the value of each cell in column A. That’s the source sentence that we want to analyze to separate words and numbers (and date).
• Next, we break down the sentence into parts separated by a space using the Split function. That creates an array of as many elements as parts separated by a space. The space can be changed by any other character such as a comma, semi-colon, etc, depending on the data and requirement.
• Before starting the second loop we initialize the variable part_length to allow getting the longest work in the sentence (see later).
  
• Now we start another loop through each part within that array, and in that loop we check the following three options:
• First we check if the part is a date, and if so, we add that value to column D. Note that the date will the format as per local settings (in the example above it converts to US format)
• If is not a date, we check if is a number with the IsNumeric VBA function. In such case we add the value to column C
• If is neither a date nor a number, then we assume is text (could be a single character/letter or a group of characters or a word). We could add each word to a different column (that would be like using Text to Columns), but in this example we rather select just one keyword. The keyword could be the one that matches a reference list of words, for example. But here, we will just get the longest word in the sentence. To do that, we get the length of the word and compared it with the other words in the sentence. Finally, we add the longest word to column B
• The macro is limited to get the last date and number in the sentence (it works well with sentences that only have one number or date, as in the example above). However, that can be easily adapted to get all numbers, or dates, etc. Similarly, it could get more words or a keyword based on a reference list as explained earlier.

This is how we separate words and numbers in cells with Excel VBA macros.

Other examples:

• How to Delete Rows Based On Criteria with Excel VBA macros
  
• How to Upload Data To Google Sheets with Excel VBA macros
  
• How to Consolidate Filtered Data From Multiple Sheets with Excel VBA macros