excel to text with column format

[xtro]

Hello,

I have a excel file that I would like to convert it to a text file with some formatting to it's columns. Please see the attached file for the data.

This is the format for the text file.

Id 9 chars
6 filler characters (let's say #)
last name - 17 chars
first name - 17 chars
middle name - 1 char
6 filler chars
Address1 - 35 chars
1 filler char
Address2 - 35 chars
1 filler char
City - 15 chars
1 filler char
State - 2 chars
1 filler char
zipcode - 5 chars
13 filler chars

How do achieve this format?
Thank you everyone...

[Leith Ross]

Hello Xtro,

Here is a sample of the output from a macro I wrote. Is this close to the output you want?

112356412######ROVERS           ROBERT           F######    12 First Street                    #                                   #JESUP          #IA#50648#############
548264752######PALMERS          ALLEN            G######    1 Harper Ave                       #                                   #JUPITER        #FL#33469#############

[xtro]

Hi Leith,

Absolutely... I think that's exactly what I am looking for. You are awesome!

Would you please post the macro....

Thanks again

[Leith Ross]

Hello Xtro,

Here is the macro code. The macro has already been added to the attached workbook. You will need to change the file path and name to what you are using. It is marked in red. If the file already exists, any data will be overwritten. If it doesn't exist, the macro will create it.

Type Text_Record
  ID As String * 9
  Filler1 As String * 6
  LastName As String * 17
  FirstName As String * 17
  MI As String * 1
  Filler2 As String * 6
  Suffix As String * 4
  Address1 As String * 35
  Filler3 As String * 1
  Address2 As String * 35
  Filler4 As String * 1
  City As String * 15
  Filler5 As String * 1
  State As String * 2
  Filler6 As String * 1
  ZIP As String * 5
  Filler7 As String * 13
  NewLine As String * 2
End Type
  
Sub WriteDataToFile()

  Dim Buffer As Integer
  Dim Cell As Range
  Dim FileName As String
  Dim Filler As String
  Dim Rng As Range
  Dim RngEnd As Range
  Dim TR As Text_Record
  
    Filler = String(16, "#")
    FileName = "C:\Xtro.txt"
    Buffer = FreeFile
      
      Set Rng = Worksheets("Sheet1").Range("A2")
      Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
    
      Open FileName For Random As #Buffer Len = Len(TR)
        For Each Cell In Rng
          With TR
            .ID = Cell
              .Filler1 = Filler
            .LastName = Cell.Offset(0, 1)
            .FirstName = Cell.Offset(0, 2)
            .MI = Cell.Offset(0, 3)
              .Filler2 = Filler
            .Address1 = Cell.Offset(0, 5)
              .Filler3 = Filler
            .Address2 = Cell.Offset(0, 6)
              .Filler4 = Filler
            .City = Cell.Offset(0, 7)
              .Filler5 = Filler
            .State = Cell.Offset(0, 8)
              .Filler6 = Filler
            .ZIP = Cell.Offset(0, 9)
              .Filler7 = Filler
            .NewLine = vbCrLf
          End With
          Put #Buffer, , TR
        Next Cell
      Close #Buffer
    
End Sub