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.

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.

  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 […]


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel