title: Prevent string array from being reformatted to scientific
body: <p>I am working on a macro that takes a column of cells with a list of standards delimited by a " " and adds them to an array if they are a unique value.  I ask the user where they would like to put the array that was created and then paste it at the specified location.  </p>
  
  <p>The problem - almost all of the standards start with "1E" and whenever the array is pasted into the sheet, any values that don't have a letter at the end are converted into scientific notation.  I can't format them as text afterward because it destroys the original value.  For example "1E0011" in the array is pasted as "1.00E+11".  If I format this as text (after it has been pasted), it reads "1E+11".  The array is Dim'd as a string.  </p>
  
  <pre><code>Sub Remove_1E_Duplicates()
  'Looks at every "1E" standard in a column and puts every unique value in an array at a user-selected destination
  
  Dim CurrentCell As String               'Used to temporarily store active cell's values
  Dim strArray() As String                'Final string with only unique values
  Dim tempArray() As String               'Temp array created by splitting CurrentCell's values
  Dim i As Integer                        'Counter used for each value in tempArray
  Dim elem As Integer                     'Counter for each element in strArray
  Dim lLoop As Long, lLoop2 As Long       'Counter for sorted array (ex: if lLoop=5, each element 0-4 is already sorted and 5+ needs to be sorted)
  Dim str1 As String, str2 As String      'Place holder for switching two elements' positions in an array when one has a lower sorted value
  Dim rListPaste As Range                 'Stores location of the user-input destination
  
  ReDim Preserve strArray(0)
  
  Do While ActiveCell &lt;&gt; vbnullvalue
  
      If ActiveCell.Value = "Access Denied" Then
          GoTo Denied
      End If
  
      CurrentCell = ActiveCell.Value
      tempArray = Split(CurrentCell, " ")
  
      For i = LBound(tempArray) To UBound(tempArray)
  
          For elem = LBound(strArray) To UBound(strArray)
              'Used if strArray is empty
              If strArray(elem) = "" Then
                  strArray(elem) = tempArray(i)
                  GoTo NextTemp
  
              'If the current element in tempArray is already in strArray, go to next element in tempArray
              ElseIf InStr(tempArray(i), strArray(elem)) Then
                  GoTo NextTemp
  
              'If all the elements in strArray have been searched and the element in tempArray is longer than 2 characters, _
              'then resize strArray and add the current tempArray element to strArray (after trimming extra spaces and returns)
              ElseIf elem = UBound(strArray) And Len(tempArray(i)) &gt; 2 And tempArray(i) &lt;&gt; Chr(13) Then
                  ReDim Preserve strArray(LBound(strArray) To UBound(strArray) + 1) As String
                  strArray(UBound(strArray)) = Replace(Trim(tempArray(i)), vbLf, "")
              End If
  
          Next elem
  
  NextTemp:       'Goes to next element in tempArray
      Next i
  
  Denied:         'After both arrays have been compared, goto next cell below current active cell
      ActiveCell.Offset(1, 0).Select
  
  Loop
  
  'This loop sorts the strArray array
  For lLoop = 0 To UBound(strArray)
  
      For lLoop2 = lLoop To UBound(strArray)
  
           If UCase(strArray(lLoop2)) &lt; UCase(strArray(lLoop)) Then
               str1 = strArray(lLoop)
               str2 = strArray(lLoop2)
               strArray(lLoop) = str2
               strArray(lLoop2) = str1
           End If
  
      Next lLoop2
  
  Next lLoop
  
  Set rListPaste = Application.InputBox _
      (prompt:="Please select destination cell", Type:=8)
  
  i = UBound(strArray) + 1
  Range(rListPaste, rListPaste.Offset(i)).Value2 = WorksheetFunction.Transpose(strArray)
  
  End Sub
  </code></pre>