Text format via vba?

Question

I am creating a copy of an excel file using vba, there is a column with numbers with preceeding zero's. the copy of the file is created but the data in this column is dropped. I need to keep the values with the preceeding zeros.

Answer 1

Convert each cell in that column to a text field prior to exporting it. That should ensure that every character is retained (and not treated like a number, which is what it sounds like is happening).

Answer 2

The best way is to pre-format the column as Text by setting Range.NumberFormat to "@". This way, if a user edits the cell, the cell will stay as text and maintain it's leading zeros. Here is a VBA example:

ActiveSheet.Range("C:C").NumberFormat = "@"

Answer 3

Given the accepted answer, it's probably not what you need, but setting a custom number format will also get the preceeding zeroes back into the displayed value.

To show a value with leading zeroes up to 8 digits, for example, set the format to 00000000, then 123 will be displayed as 00000123.

Both the method here and the format-as-text method will result in cell values that will still work in calculations, although horizontal alignment will be different by default. Note also that, for example, concatenating strings to the values will result in differences:

as text: displays 00000123, append "x" to get 00000123x

as number with custom format: displays 00000123, append "x" to get 123x, because it's still really a number.

Probably TMI, though!

Answer 4

This is the code I have created to resolve this issue: Public Sub Change_10_Digit() '---------------------------------------------------------------------------- ' Change numeric loan number ot a 10 digit text number ' 2010-05-21 by Jamie Coxe ' ' Note: Insure exracted data Column is formated as text before running macro '----------------------------------------------------------------------------

    Dim Lastrow As Long
    Dim StartRow As Long
    Dim Col As String
    Dim RowNum As Long
    Dim nCol As String
    Dim Loan As String
    Dim Digit_Loan As String
    Dim MyCell As String
    Dim NewCell As String
    Dim Cell_Len As Long
    Dim MyOption As Long

'----- Set Options -------------------------------------------------------

MyOption = 2 '1 = place data in new column, 2 = Replace data in cell
StartRow = 2 'Start processing data at this row (skip header row)
Col = "B" 'Loan number in this colmun to be changed to 10 digit
nCol = "G" 'New column to place value (option 1 only)

'----- End Option Setings ------------------------------------------------

'Get last row
Lastrow = Range(Col & "65536").End(xlUp).Row

For RowNum = StartRow To Lastrow
    'Combined Column and Row number to get cell data
    MyCell = Col & RowNum

    'Get data in cell
    Loan = Range(MyCell).Value

    'Change data in cell to 10 digit numeric with leading zeros
    Digit_Loan = Format(Loan, "0000000000")

    If My0ption = 1 Then
        'Option 1 enter value in new cell
        NewCell = nCol & RowNum
        Range(NewCell).Value = Digit_Loan
    Else
        'Option 2 replace value in cell
        Range(MyCell).Value = Digit_Loan
    End If

Next RowNum

End Sub