title: excel vba split text
body: <p>Please be aware that I am working with a series of ~1000 line medical information databases.  Due to the size of the databases, manual manipulation of the data is too time consuming.  As such, I have attempted to learn VBA and code an Excel 2010 macro using VBA to help me accomplish parsing certain data.  The desired output is to split certain characters from a provided string on each line of the database as follows:</p>
  
  <p>99204 - OFFICE/OUTPATIENT VISIT, NEW</p>
  
  <p>will need to be split into</p>
  
  <p>Active Row Active Column = <em>99204</em> ActiveRow Active Column+3 = <em>OFFICE/OUTPATIENT VISIT, NEW</em></p>
  
  <p>I have researched this topic using Walkenbach's "Excel 2013: Power Programming with VBA" and a fair amount of web resources, including this awesome site, but have been unable to develop a fully-workable solution using VBA in Excel.  The code for my current macro is:</p>
  
  <pre><code>Sub EasySplit()
  Dim text As String
  Dim a As Integer
  Dim name As Variant
  text = ActiveCell.Value
  name = Split(text, "-", 2)
  For a = 0 To 1
  Cells(1, a + 3).Value = Trim(name(a))
  Next a
  End Sub
  </code></pre>
  
  <p>The code uses the "-" character as a delimiter to split the input string into two substrings (I have limited the output strings to 2, as there exists in some input strings multiple "-" characters).  I have trimmed the second string output to remove leading spaces.</p>
  
  <p>The trouble that I am having is that the output is being presented at the top of the activesheet, instead of on the activerow.</p>
  
  <p>Thank you in advance for any help.  I have been working on this for 2 days and although I have made some progress, I feel that I have reached an impasse.  I think that the issue is somewhere in the</p>
  
  <pre><code>Cells(1, a + 3).Value = Trim(name(a))
  </code></pre>
  
  <p>code, specifically with "Cells()".</p>
  
  <p>Thank you Conrad Frix!</p>
  
  <p>Yah.. funny enough.  Just after I post I have a brainstorm.. and modify the code to read:</p>
  
  <pre><code>Sub EasySplit()
  Dim text As String
  Dim a As Integer
  Dim name As Variant
  text = ActiveCell.Value
  name = Split(text, "-", 2)
  For a = 0 To 1
  ActiveCell.Offset(0, 3 + a).Value = Trim(name(a))
  Next a
  End Sub
  </code></pre>
  
  <p>Not quite the colkumn1,column4 output that I want (it outputs to column3,column4), but it will work for my purpose.</p>
  
  <p>Now I need to incorporate a loop so that the code runs on each successive cell in the column (downwards, step 1) skipping all bolded cells, until it hits an empty cell.</p>