Keep / Assign variable leading zeros

Rusty

Board Regular
Joined
Apr 8, 2002
Messages
108
Hi,

Am importing a load of data from a text file, splitting into columns, amending in excel then outputting a text file.

One (or more) of the columns has leading zeros which I want to retain.

eg:

000000000915000
000000000915000
000000004340000
000000003623130
000000003623130

Which comes in as:

915000
915000
4340000
3623130
3623130

Which is fine. But I want to make sure that when I output the text file, that they are all (in this case) 15 characters in length.

Therefore, to assign a variable number of leading zeros dependent on the cell value.

I'm sure I could write a macro to calculate cell by cell, but is there an easy way to do it all in one hit?

Thanks
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Rusty

Board Regular
Joined
Apr 8, 2002
Messages
108
Heres the code...

Code:
Sub Journal_Import_Format_Import_File()

    Dim Orig_File As String
    Orig_File = Range("Cover!E3")
    
    Dim Location As String
    Location = Range("Cover!E5")
    
    Dim Out_File As String
    Out_File = Orig_File & "X"
    Range("Cover!E7") = Out_File
    
    Dim File_Ext As String
    File_Ext = Range("Cover!F3")
    
    Sheets("Work").Select
    Cells.Delete
    Columns("H:H").Select
    Selection.NumberFormat = "@"
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Location & Orig_File & "." & File_Ext, _
        Destination:=Range("A1"))
        .Name = Orig_File
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(15, 7, 8, 2, 1, 7, 7, 18, 1, 1, 5, 5, 15, 25, 8, 7, 8, 6, 8, 9, 8, 7, 1, 10, 5, 5, 18, 18, 1, 5, 4, 1, 1, 1, 1, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 8, 1, 1, 5, 3, 15, 88)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

Rusty

Board Regular
Joined
Apr 8, 2002
Messages
108
Have written code to deal with all possible scenarios... anyone want?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,676
Messages
5,573,603
Members
412,538
Latest member
mrjack
Top