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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,021
Messages
5,834,992
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top