locked
Convert numbers stored as numbers to numbers stored as text RRS feed

  • 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


    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


    Sunday, October 30, 2016 8:45 PM