Export Excel as Fixed Width Text File Format using VBA

Although the reverse is easy, export of an excel worksheet as a fixed width text file (.txt) file is sometimes is not. Now, Excel does provide you with the option to save a worksheet as a text (.txt) or a Comma Separated (.csv) file, but if you had to ensure that each of the fields have their own specified lengths, this job is far from easy.


I recently came across a situation where we had to exchange data with a client whose system had a set specification. Each of the fields had a specific width (# of chars) and a simple comma separated or plain text file would not board in their systems properly. So yours truly came up with this simple utility that can helps you export an excel worksheet as a fixed width text file.

All you need to do to convert the data in an excel spreadsheet into a fixed width text file is to just select the data range that you want to export and click the ‘Export to Fixed Length Format’ button. Just ensure that the length of the fields are specified in the first row of the worksheet. Essentially what happens in the back end is that the VBA code looks at each row in the selection and for each of the fields in that row, reads the width to be assigned from the column header. Just a disclaimer – This code is neither ‘fool-proof’ nor has been tested for performance. But I am sure this would work like a charm for most of your day to day needs.

In its current format, this tool uses a space (” “) as the filler incase the data in a cell is shorter than the width specified for that column. If you want to change that, go to the VBA code and edit the “Filler_Char_To_Replace_Blanks” variable and set it to the one that you’d like to use. Let me know how this works out for you.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Sophie wrote:

    great, tank you! just what I needed. one question: is there an easy way to modify so that the values would be right-bounded in the output file? I am trying to figure out, but couldn’t up to now.

  2. Ajay wrote:

    Glad I could be of help. See if replacing the Print statement with the one given below in the VBA code works for you.

    Print #FileNum, Format$(” “, “!” & String(FieldWidth, “@”)) & Format$(CellValue, “#”);

    I promise I will look at this code in a little more detail when I get some more time but you’ll have to wait for that a little.

  3. Vincent wrote:

    Hi, thanks alot for sharing this. Really useful to me. Thanks 😀

  4. Ajay wrote:

    Hi Vincent ! Glad this was of help.

  5. Lisa wrote:

    Wow…I’ve been looking for something that would do this and it worked like a charm! Thanks a million!

  6. Ajay wrote:

    Lisa. Happy to see that it helped.

  7. wax wrote:

    can u tell me how to set the fixed length?Is that just key in the number in every first row without any setting?

  8. Ajay wrote:

    Hi wax,

    That’s true. Just enter the number in the first row and that will be picked up as the width of that particular field in the output text file. Just copy the workbook to a folder and the output text file will be stored in the same folder. You can import the text file into excel and check the width of the fields at the time of import.


  9. Roger wrote:


    very helpful – thanks – but like Sophie, I need to right justify. Tried the code you suggested but it garbled. Something like this is going to save me a lot of wasted time getting spreadsheet data into mathematical models that need particularly formatted data

  10. Ajay wrote:


    Haven’t spent too much time on hacking this out but can you try the code given below and check if the fixed width file comes out with leading spaces and right justified.

    Sub Export_Selection_As_Fixed_Length_File_Right_Align()
        Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
        Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
        Dim sht As Worksheet

        Filler_Char_To_Replace_Blanks = " "
            If Selection.Cells.Count < 2 Then
                MsgBox "Nothing selected to export"
            End If

        DestinationFile = ActiveWorkbook.Path & "/File_With_Fixed_Length_Fields.txt"
        FileNum = FreeFile()
        On Error Resume Next
        Open DestinationFile For Output As #FileNum
         ' If an error occurs report it and end.
       If Err <> 0 Then
             MsgBox "Cannot open filename " & DestinationFile
        End If
         ' Turn error checking on.
       On Error GoTo 0
         '  Loop for each row in selection.
       For RowCount = 1 To Selection.Rows.Count
                    For ColumnCount = 1 To Selection.Columns.Count
                        CellValue = Selection.Cells(RowCount, ColumnCount).Text
                        If (IsNull(CellValue) Or CellValue = "") Then CellValue = Filler_Char_To_Replace_Blanks
                        FieldWidth = Cells(1, ColumnCount).Value
                        If (ColumnCount = Selection.Columns.Count) Then
                                Print #FileNum, Application.WorksheetFunction.Rept(" ", Application.WorksheetFunction.Max(0, FieldWidth - Len(CellValue))) & CellValue & vbCrLf;
                        Else: Print #FileNum, Application.WorksheetFunction.Rept(" ", Application.WorksheetFunction.Max(0, FieldWidth - Len(CellValue))) & CellValue;
                        End If
                    Next ColumnCount
        Next RowCount
         ' Close destination file.
       Close #FileNum
        Workbooks.OpenText Filename:=DestinationFile
    End Sub


  11. Dean Jarratt wrote:

    Superb Macro…I am using this to import address information into a shipping system…this is exactly what I needed…Thanks very much.


  12. Ajay wrote:


    Glad this was of help 🙂 and welcome to da TaB !


  13. Mark wrote:

    Very cool and exactly what we needed – you rock!

  14. Ajay wrote:

    Mark – Glad it helped. Welcome to da TaB !

  15. gary wrote:

    i’m having difficulty.

    my spreadsheet is 16 columns. when i convert, the file_with_fixed_length_fields.txt has some columns rearranged. column F has become 2 columns (F and G), columns G, H, I have been combined into column I, with a portion of column H being placed in column I, and columns J and K combined into column J. the other columns, both before and after the rearranged ones, are converted correctly.

    any ideas what i may be doing wrong?

  16. gary wrote:

    additional information related to first post.

    despite the column issue, when the .txt file is displayed as a text file, the alignment is ok.

    but looking at the problem closer, it appears that the left most characters are being truncated. it appears this is when the data within a cell is larger than the defined length. as example, if the column is defined as being 7 characters, but one cell within that column has 8 characters, such as Schedule, then the first character is truncated, displaying chedule.

    if this analysis is correct, is there a way to have the extra characters on the right truncated instead? i’ve solved the problem by first editing the data using an excel LEFT function.

  17. Ajay wrote:

    @Gary – Interesting question. Here’s the VBA implementation of the what you’ve suggested. Just place this line right before the “On Error GoTo 0” statement in the original code used to export the text file.

    '------usual code here--------

    For Each cel In Selection
        If Len(cel.Value) > Cells(1, cel.Column).Value Then
            cel.Value  = Left(cel.Value, Cells(1, cel.Column).Value)
        End If
    Next cel

    '------usual code here--------

    Essentially this will check if any cell has a value greater in length than specified in the column header and then truncate it by removing the rightmost characters. Hope this helps.

  18. gary wrote:

    hello ajay.

    thanks for feedback.

    either i’m doing something wrong (most likely) or there is a bug.

    the fields continue to be truncated on left.

  19. Ajay wrote:

    @Gary – Try this file with right truncate and let me know if it works for you 🙂

  20. gary wrote:

    hello again ajay.


    thanks alot.

  21. Ajay wrote:

    @Gary – yahoo 😉

  22. fidelis wrote:

    the trick worked for me,

    Thank you


  23. Ajay wrote:

    @fidelis – Thanks and welcome to da TaB !

  24. JJ wrote:

    This is great, the only thing i am lacking for my application is the ability to have a delimiter with the fixed width. Where would I put it in on the code?

  25. Ajay wrote:

    @JJ – You can assign a value to ‘Filler_Char_To_Replace_Blanks’ to be used as a the delimiter. See if that helps.

  26. Hanna wrote:

    Thanks a lot – just what I needed to make progress at work!
    It is my first time with Macros and it ran so smoothly…amazing!
    Thanks indeed for sharing.

  27. Ajay wrote:

    @Hanna – Thanks. Glad it worked !!

  28. marko wrote:

    Hi to all.
    thnx for this superb macro.
    It helps a lot.

    Just one more question:
    how could I do, that string would be left aligned (like now), and numbers would be right aligned in the same file?


  29. Melissa wrote:

    I have looked for years to find something to do this with, this works great, thanks.

  30. joti wrote:

    Ajay, thanks for the great code! Very helpful!
    I have decimal values in the cells that I’m trying to extract values from, for e.g. 58.9 or -76.1 but this code truncates anything after the decimal point. Is there a way to extract all the decimal places as well? I tried with “Range” type but it didn’t work..

  31. joti wrote:

    In regards to my post above, I somehow resolved the decimal problem, so it works fine now.
    However, I have some cells that contain upto150 characters each. So when it exports to the excel file, half the text in the original cell is broken up into 3 different columns – A, B & C. I’ve tried manipulating the original text but still the same problem. What am I doing wrong??

    Here’s an example from the exported excel file:
    Column A: Headend Driver Amplifier (QRAM860(23dB) input = 15dBmV: outpu
    Column B: t =
    Column C: 38dBmV)

    It should just appear in one column as:
    Headend Driver Amplifier (QRAM860(23dB) input = 15dBmV: output =38dBmV)

    Please help…

  32. Peter J wrote:

    Thanks – this macro is very helpful, much better than the other example I found for fixed width in the ease of dealing with blank cells. But I am new to macros and I can’t understand what is making the values also show up as a new worksheet with the same name as the file name, in addition to being written to the txt file?

  33. Peter J wrote:

    Sorry – I guess I answered my own question

    Workbooks.OpenText Filename:=DestinationFile

    For some reason I could not see that last line of text until I entered some returns.

  34. benjie wrote:

    Great utility! Works great!

    Is there a way I could specify my data to be right justified instead of left?


  35. Agus Margono wrote:

    Thank’s from Indonesia…. this tool help me alot…..

  36. Kelvin wrote:

    Thanks for this file. It has helped a lot. I was able to incorporate the code to truncate the characters of those cell values greater than the defined field length. I also included the right justified code. However, this code adds leading blank spaces in order to right justify it. How can I change the blank spaces to zeros?

  37. Kelvin wrote:

    I ended up doing a macro to have a customized format for each column. This worked because the extraction to the text file maintains the format in which the excel is placed.

    So, I first run the macro to add the leading zeros via the custom format, then the macro to export the data to the text file and it works great.

    Thanks once again

  38. Akmal wrote:

    My first column always combined with 2nd column.. can you help?

  39. Crews wrote:

    Thanks,that is very useful for me.

  40. Jonas H wrote:

    Thank you Ajay for this nice little macro!
    I has saved me a lot of time when implemented into mine macro.
    The align right patch you wrote for Roger works fine btw

  41. Hemanth wrote:

    Excellent utility and very easy to use. Thanks a lot.Worked like a charm and saved a lot of work. Keep up the good work.

  42. DavidC wrote:

    This works PERFECTLY except in my application I need a way to change the justification

    I have some columns that are left justified and some are right justified.

    Is it possible to do this?

    Thanks, this is excellent!

  43. Teresa wrote:

    Thanks for the macro info. Just what I was looking for. I do have one dilemma though. I need to output two different record types in the same file. One is a header followed by detail. The header has different field lengths than the detail. Any suggestions?

  44. Marco Ramírez wrote:

    That´s great¡¡¡

  45. Dan from Madison, WI wrote:

    Thank you! Just what I was looking for.

    Madison, WI

  46. Vicki Stringfellow wrote:

    Hello, I have two questions: First, you said “select the data range that you want to export and click the ‘Export to Fixed Length Format’ button.” I’m using Excel 2007 and I can’t find a button “Export to Fixed Length…” Also, I have columns with headers which have just blanks in the cells below which have fixed lengths but no data. Will spaces get created for those columns = to the number used in the first cell? I’m sorry, I’m having problems, seems like everyone loves this program and I’m thrilled to have found it, just not off to a good start! Thanks,

  47. Douglas wrote:

    Thanks so much!!

    What I really need though is to be able to have some fields left justified and others right justified.

    Can you assist?

  48. Georg wrote:

    Great stuff, saved me weeks 🙂

  49. Chris wrote:

    This is great code Ajay!! How would I alter the code so that the txt file can be archived by date so saveas ABC File Name 2011.4.7 rather than just saving it with the same file name each time?

  50. Jeff wrote:

    Awesome code, and about one tenth the length we used with a “string & fill” method.

  51. Wayne wrote:

    You saved me a ton of work. You’re a master!! Thanks for putting this tool together.

  52. BIBIE wrote:

    PLEASE, i DONT SEE THIS ‘Export to Fixed Length Format’ button HERE?

  53. Rogelio wrote:

    Hi, thanks a lot for sharing this. Just what I needed… THANKS!!

  54. Dianne Bilbrey wrote:

    This utility is fabulous!! It’s incredibly easy and saved MANY hours of headache!!

  55. Raul wrote:

    I don’t know you man, but you rock !!! Thank you very much for this macro.

  56. chip wrote:

  57. Erika wrote:

    Great idea, but I’m having issues with the macro. I’m starting with a 7-column spreadsheet, but the resulting spreadsheet and txt file both combine columns 1-3 into a single mass of text. The remaining 4 columns are also not justified, each row starts and stops at different places. Any ideas?

  58. neeraj wrote:

    Thanks for your efforts Ajay! works nicely!

  59. Pekka wrote:

    Thanks alot Ajay! Right fixed works nice.

  60. Bram wrote:

    Hello, not really sure if you’re still alive but here it goes..

    I like your code! But I would like to hard code the fixed width because every export I’m doing is the same! So instead of putting the fixed width in the upper column, I would like to hard code it. Any ideas?

  61. Ashraf wrote:

    It is very superb macro which you had build are there any way out where in we can remove the selection criteria because some time the file may be large and selection will take more time.

  62. Tara wrote:

    This is a fantastic tool. I’ve always used “Save As | Formatted Text (Space Delimited) (*.prn)” to create fixed width files, but unfortunately .prn is limited to 240 characters in width. Your tool solves the width problem and let’s me export all 308 characters width!

    A question: I need to add a header and trailer to my fixed width text files. The header and trailer have different column widths than the main file details, so I can’t export everything at once. Currently I have my excel book set up as tab1 = header, tab2 =details, tab3=trailer. Is there a way to add to the vba code do process all 3 tabs in order and save them to 1 file?

    *Crossed fingers*

  63. Aashish wrote:

    Awasome !! this works. Thank you very much.
    Just one help , to add leading 0 what should need to be changed ? if the cell length is 3 char but value is 1 then output should be 001, how can I do it ?
    could you please suggest?

  64. Courtney wrote:

    This was super helpful for a last minute report I had to file in a very specific format. What a legend for posting this.

  65. Patrick wrote:

    I would like to convert files, that have a cell width up to 5000 characters. Unfortunately I always receive an error message. Is this because of the 255 character limitation? Is there work around for that?

  66. Patrick wrote:

    After further testing I was able to create space limited text file with columns up to a defined with of 1013 characters.
    If I go over an error appears and when I open the “debug” mode the following line is marked yellow:
    “Print #FileNum, Format$(CellValue, “!” & String(FieldWidth, “@”)) & vbCrLf;”

    Unfortunately I am not that code savvy to understand what it tells me exactly. Does anybody know?

    In my case the last column requires a fixed width with 5000. For the time being I can split it up in multiple columns to get the width of 5000. Luckily I never exceeded 1013 characters so I dont have to split the text.


  67. Michael wrote:

    Just found your export Excel as fixed width text file format VBA macro which is perfect and exactly what I need. The issue is that one column needs to be 1024 characters and I get VBA errors when the column characters is 1014 or greater. Is there a reason for this

  68. Max wrote:

    Wow! Awesome script and worked like a charm. Thanks so much. This just saved me hours of work manually formatting a file from excel for an EDI transfer!

  69. Ralf wrote:

    Hi, this is awesome!
    Thanks for the effort and for providing the code.
    MS should be all over this to get rid of *.prn and it’s 240 character limitation.
    Keep up the great work!


  70. Vijay wrote:

    Great Macro ..very useful. Just one help, the fixed length file is created with Carriage Return and New Line character.

    Is there a way to modify the code to generate the fixed length file with only new line character.

  1. Diigo Diary 04/15/2010 « Benx Blog wrote:

    […] Export Excel as Fixed Width Text File Format using VBA | Excel & VBA – da Tab Is On […]

  2. How To Export Excel Spreadsheet To A Fixed-Width Text File | KC's Blog wrote:

    […] I am luck to find out is this with VBA. It’s a far more flexible solution that lets you export a spreadsheet to a perfectly lined up […]

  3. Export Excel as Fixed Width Text File | Wookjin wrote:


Keep up with the latest stories - Delivered right to your inbox
treeemap software for excel