Answered by:
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?
Saturday, October 29, 2016 9:07 PM
Answers

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)
 Marked as answer by THTC Sunday, October 30, 2016 6:01 AM
Saturday, October 29, 2016 9:47 PM 
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
 Proposed as answer by Edward8520Microsoft contingent staff Monday, October 31, 2016 5:34 AM
 Marked as answer by THTC Monday, October 31, 2016 5:41 AM
Sunday, October 30, 2016 8:45 PM
All replies

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)
 Marked as answer by THTC Sunday, October 30, 2016 6:01 AM
Saturday, October 29, 2016 9:47 PM 
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
 Proposed as answer by Edward8520Microsoft contingent staff Monday, October 31, 2016 5:34 AM
 Marked as answer by THTC Monday, October 31, 2016 5:41 AM
Sunday, October 30, 2016 8:45 PM