In Excel, How would you create a For Loop that loops within a cell?

Question

How would you create a For Loop that loops within a specific cell? I want to make a code that loops for each 'word' (separated by spaces) within a cell. So if a cell contains the string 'the dog has fleas', I want my code to loop on 'the', then on 'dog, then on 'has' and finally on 'fleas' before stopping.

siti Vi · Accepted Answer

assuming your sentence  "the dog has fleas" is in range B2

Sub BlaBlaBla()
   Dim T As String
   Dim k As String
   Dim i As Integer
   Dim r As Integer

   T = Trim(Range("B2").Text) & " "

   For i = 1 To Len(T)
      k = k & Mid(T, i, 1)
      If Mid(T, i, 1) = " " Then
         r = r + 1
         Range("B2").Cells(r, 3) = k
         k = ""
      End If
   Next

End Sub


' The result
D2  = the 
D3 = dog 
D4 = has 
D5 = fleas

In Excel, How would you create a For Loop that loops within a cell?

Related Questions

Trending Questions