Break Cell Value On Capital Letter

If you want to beak the words in a cell on the basis of Capital Letter. Snapshot Below-

Here is the code-


Sub break_word_on_capital_letters()
Dim i As Long, j As Long, z As Long, x As Long, d As Long
Dim k As Boolean
Dim st As Long
Dim abc, xyz As String
  z = Range("A1048576").End(xlUp).Row
For x = 1 To z
d = 1
st = 2
Range("a" & x).Select
abc = ActiveCell.Value
For i = st To Len(abc)
If i > Len(abc) Then
Range("a" & x).Offset(0, d).Value = abc
Exit For
End If
If Asc(Mid(abc, i, 1)) > 64 And Asc(Mid(abc, i, 1)) < 91 Then
Range("a" & x).Offset(0, d).Value = Left(abc, i - 1)
xyz = Right(abc, Len(abc) - i + 1)
abc = xyz
i = 1
d = d + 1
End If
Next i
If Len(Range("a" & x).Value) = Len(abc) Then
Range("a" & x).Offset(0, 1).Value = Range("a" & x).Value
End If
Next x
End Sub

Download Excel Workbook