title: Text to Rows VBA Excel
body: <p>I have a spreadsheet with about a good 4000 rows of data, one of the columns of data has unique order numbers that I want separated using "/" as my delimiter. So essentially I want:</p>
  
  <pre><code>Name      Order#       Date
  Jane      123/001/111  08/15/2013
  Gary      333/121      09/01/2013
  Jack      222          09/02/2013
  </code></pre>
  
  <p>To look like this:</p>
  
  <pre><code>Name      Order#       Date
  Jane      123          08/15/2013
  Jane      001          08/15/2013
  Jane      111          08/15/2013
  Gary      333          09/01/2013
  Gary      121          09/01/2013
  Jack      222          09/02/2013
  </code></pre>
  
  <p>I am fairly new to VBA, so I decided to try to Google for a solution where I came upon this nice bit of code.</p>
  
  <pre><code>        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
      Dim ans 
      Dim Cels As Long, i As Long 
      Cancel = True 
      ans = Split(Target, ":") 
      Cels = UBound(ans) 
      Target.Offset(1).Resize(Cels).EntireRow.Insert shift:=xlDown 
      Rows(Target.Row).Copy Cells(Target.Row + 1, "A").Resize(Cels) 
      For i = 0 To Cels 
          Target.Offset(i) = ans(i) 
      Next 
  End Sub
  </code></pre>
  
  <p>It works great but the way this macro functions is that you have to double click the row to separate the values. What I was hoping for is a way to pass this function through a For loop so it executes throughout the entire spreadsheet.</p>