title: Converting from string to date
body: <p><strong>Short story:</strong></p>
  
  <p>My question is two fold: </p>
  
  <ol>
  <li><p>How do I copy over this text <code>05-Jun-2014</code> from a text file so that the text <code>05-Jun-2014</code> does not get displayed as <code>#NAME?</code> or get cut off at <code>5</code>? (I need the whole thing copied over.)</p></li>
  <li><p>How do I take that text and make it a date?</p></li>
  </ol>
  
  <p><strong>Long story</strong></p>
  
  <p>In this text file I have dates in this format <code>day-month-year</code>, e.g <code>05-Jun-2014</code>. 
  As you see, the "e" in June is cut off. If I type in <code>05-Jun-2014</code> in excel, it recognizes it. I use <code>CDate</code> to convert the string to a date.</p>
  
  <p>My problem is multi-fold. Essentially I want excel vba to recognize <code>05-Jun-2014</code> as a date.</p>
  
  <p>If I try to copy the phrase <code>05-Jun-2014</code> from my text document. I will only get <code>5</code> to copy over. I copy the string to a jagged array <code>jaggArray</code> and from there I call it.</p>
  
  <p>Below you can see my code for how I copy text: </p>
  
  <pre><code>Private Function createGCStruct(ByRef tempString() As String, ByVal aNum As Integer, ByVal oNum As Integer, _
  ByRef oList() As String, ByVal index As Integer, ByVal gcSoiDate As Date, ByVal gcFName As String, _
  ByVal typeName As String) As gcBStruct
  
  '...code...
  
  ' JaggedArray:
  Dim jaggArray() As Variant
  
  '...code...
  
  '2) Capture structure information from textfile array
  
      'A) remove unnecessary spaces from existing Array and Place into jaggedArray
      jaggArray = splitStringArrayElements(tempString)
  
  '...code...
  
  End Function
  </code></pre>
  
  <p>splitStringArrayElements Function():</p>
  
  <pre><code>Private Function splitStringArrayElements(tempString() As String) As Variant()
  ' 1) Variables:
      Dim j As Long
      Dim trimmedString As String
      Dim jaggArray() As Variant
      ReDim jaggArray(LBound(tempString()) To UBound(tempString()))
      Dim emptyStringArrayPlaceholder() As String
      ReDim emptyStringArrayPlaceholder(0 To 0)
  ' 2) Remove unwanted spaces from tempString() and create a JaggedArray
      For j = LBound(tempString()) To UBound(tempString())
          ' Remove spaces inbetween
          trimmedString = Trim(tempString(j))
          ' If empty line, array is empty
          If trimmedString = "" Then
              jaggArray(j) = emptyStringArrayPlaceholder
          ' Else add array without spaces
          Else
              jaggArray(j) = SplitAdv(trimmedString, " ")
          End If
      Next j
  
      splitStringArrayElements = jaggArray
  End Function
  </code></pre>
  
  <p>splitAdv() Function:</p>
  
  <pre><code>Public Function SplitAdv(ByVal InputText As String, Optional ByVal Delimiter As String) As Variant
  
  ' This function splits the sentence in InputText into
  ' words and returns a string array of the words. Each
  ' element of the array contains one word.
  
      ' This constant contains punctuation and characters
      ' that should be filtered from the input string.
      Const CHARS = """-" 'Potential options are: "!?,.;:""'()[]{}"
      Dim strReplacedText As String
      Dim intIndex As Integer
  
      ' Replace tab characters with space characters.
      strReplacedText = Trim(Replace(InputText, _
           vbTab, " "))
  
      ' Filter all specified characters from the string.
      For intIndex = 1 To Len(CHARS)
          strReplacedText = Trim(Replace(strReplacedText, _
              Mid(CHARS, intIndex, 1), " "))
      Next intIndex
  
      ' Loop until all consecutive space characters are
      ' replaced by a single space character.
      Do While InStr(strReplacedText, "  ")
          strReplacedText = Replace(strReplacedText, _
              "  ", " ")
      Loop
  
      ' Split the sentence into an array of words and return
      ' the array. If a delimiter is specified, use it.
      'MsgBox "String:" &amp; strReplacedText
      If Len(Delimiter) = 0 Then
          SplitAdv = VBA.Split(strReplacedText)
      Else
          SplitAdv = VBA.Split(strReplacedText, Delimiter)
      End If
  End Function
  </code></pre>
  
  <p>If I remove the <code>-</code> when I copy, the three parts become: <code>5-Jan-00  #NAME?  14:42:58</code></p>
  
  <p>Here is where I copy over the date to a structure (assuming i removed the "-"). My goal was to copy over the parts of the date "<code>05</code>", "<code>Jun</code>", and "<code>2014</code>", and then recombine them in this format: <code>05 Jun,2014</code>, and then try to convert that to a date: </p>
  
  <pre><code>Dim rDStart As Integer      ' row of first gcDate
  Dim cDStart As Integer      ' col of first gcDate
  Dim tempD1 As Date
  Dim tempD2 As Date
  rDStart = 6                                            ' the row of the first gcDate
  cDStart = 2                                            ' the column of the first gcDate
  'Collect gcDate
  '***I get my errors below***
  tempD1 = CDate(jaggArray(rDStart - 1)(cDStart - 1) &amp; " " &amp; jaggArray(rDStart - 1)(cDStart) &amp; ", " &amp; jaggArray(rDStart - 1)(cDStart + 1))
  tempD2 = CDate(jaggArray(rDStart - 1)(cDStart + 2))
  createGCStruct.gcDate = tempD1 + tempD2
  </code></pre>