Convert numbers stored as numbers to numbers stored as text

Question

I have a column with cells that contain numbers. I want to convert the numbers to numbers stored as text and either replace the original numbers with the numbers stored as text or write the numbers stored as text out to a new range. 

Sub Test1()

  Dim ar_Array() As Variant

  Dim N As Long

 

  ar_Array = Range("A1:A10")

  For N = 1 To UBound(ar_Array, 1)

    ar_Array(N, 1) = CStr(ar_Array(N, 1))

  Next N

  Range("B1").Resize(UBound(ar_Array)) = ar_Array

End Sub

I can verify that the Array contains strings rather than numbers after the Cstr loop (all elements are in quotes—“345” for example instead of 345).

But when the Range(“B1”) statement executes it outputs ordinary numbers to the worksheet cells.

For performance purposes I’m trying to avoid looping through all the worksheet cells to convert the values to text, which is why I’m using an array in the first place. 

Any idea what I’m doing wrong?

Answer 1

You could change

    ar_Array(N, 1) = CStr(ar_Array(N, 1))

to

    ar_Array(N, 1) = "'" & ar_Array(N, 1)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

I found another solution.  This one also works and it doesn't require inserting the " ' " character.  

The solution also optionally sets a number of leading zeros for the value converted to text (passed as a parameter from the calling procedure). Here's the code in case any one's interested:

Sub Convert_To_Text(ByVal N As Integer)
'N is the number of zeros in the formatted result.
  Dim M As Long
  Dim Q As Long
  Dim str_Zeros As String     'Format codes.
  Dim ar_ColOut As Variant
  
  ar_ColOut = Selection
  
'Set number of zeros passed from calling proc (N).
  str_Zeros = ""
  Q = 1
  If N > 0 Then
    Do Until Q > N
      str_Zeros = str_Zeros & "0"
      Q = Q + 1
    Loop
  Else
    str_Zeros = "###"
  End If
  
  For M = 1 To UBound(ar_ColOut, 1)
    ar_ColOut(M, 1) = CStr(Format(ar_ColOut(M, 1), str_Zeros))
  Next M
  
  ActiveCell.Offset(, 1).Resize(Selection.Rows.Count) _
    .NumberFormat = "@"
  ActiveCell.Offset(, 1).Resize(Selection.Rows.Count) = ar_ColOut
End Sub