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.
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Table Formula in Excel (Something I didn’t Know Till Yesterday) 
Multithreaded VBA – An Approach To Processing Using VBScript 
Named Range – A Few Quickies 
So How Many Of These Excel Shortcuts Do You Know, Punk ? 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
hello!
May 21st, 2009 at 1:09 amgreat, 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.
sophie
Sophie,
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.
May 21st, 2009 at 2:42 pmAjay
Hi, thanks alot for sharing this. Really useful to me. Thanks 😀
July 1st, 2009 at 10:05 pmHi Vincent ! Glad this was of help.
July 1st, 2009 at 10:51 pmWow…I’ve been looking for something that would do this and it worked like a charm! Thanks a million!
July 6th, 2009 at 1:07 pmLisa. Happy to see that it helped.
July 6th, 2009 at 2:44 pmcan u tell me how to set the fixed length?Is that just key in the number in every first row without any setting?
July 10th, 2009 at 5:04 amHi 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.
🙂
July 10th, 2009 at 1:26 pmAjay,
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
August 25th, 2009 at 5:30 amRoger,
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.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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"
Selection.Activate
End
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
Selection.Activate
End
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
Selection.Activate
Workbooks.OpenText Filename:=DestinationFile
End Sub
Regards,
August 25th, 2009 at 2:03 pmSuperb Macro…I am using this to import address information into a shipping system…this is exactly what I needed…Thanks very much.
Cheers
September 14th, 2009 at 1:57 pmDean,
Glad this was of help 🙂 and welcome to da TaB !
Regards,
September 14th, 2009 at 3:02 pmVery cool and exactly what we needed – you rock!
November 13th, 2009 at 12:58 pmMark – Glad it helped. Welcome to da TaB !
November 13th, 2009 at 1:05 pmi’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?
November 20th, 2009 at 1:49 pmadditional 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.
November 20th, 2009 at 4:58 pm@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.
2
3
4
5
6
7
8
9
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.
November 20th, 2009 at 10:51 pmRegards,
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.
November 23rd, 2009 at 3:42 pm@Gary – Try this file with right truncate and let me know if it works for you 🙂
November 23rd, 2009 at 10:38 pmhello again ajay.
perfecto!!!!
thanks alot.
November 25th, 2009 at 7:50 am@Gary – yahoo 😉
November 25th, 2009 at 1:00 pmthe trick worked for me,
Thank you
fk
December 2nd, 2009 at 6:25 am@fidelis – Thanks and welcome to da TaB !
December 2nd, 2009 at 3:12 pmThis 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?
January 19th, 2010 at 3:03 pm@JJ – You can assign a value to ‘Filler_Char_To_Replace_Blanks’ to be used as a the delimiter. See if that helps.
January 19th, 2010 at 11:07 pmRegards,
Hi,
March 7th, 2010 at 3:22 pmThanks 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.
@Hanna – Thanks. Glad it worked !!
March 8th, 2010 at 11:05 pmHi 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?
thanks
March 15th, 2010 at 2:48 amI have looked for years to find something to do this with, this works great, thanks.
May 11th, 2010 at 3:08 pmAjay, thanks for the great code! Very helpful!
July 14th, 2010 at 10:45 amI 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..
Joti
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…
July 15th, 2010 at 7:49 amThanks – 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?
July 27th, 2010 at 1:29 pmSorry – 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.
July 27th, 2010 at 1:50 pmGreat utility! Works great!
Is there a way I could specify my data to be right justified instead of left?
Cheers!
July 28th, 2010 at 11:41 pmThank’s from Indonesia…. this tool help me alot…..
September 16th, 2010 at 4:14 amThanks 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?
September 23rd, 2010 at 9:42 amI 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
September 23rd, 2010 at 12:27 pmMy first column always combined with 2nd column.. can you help?
October 14th, 2010 at 12:21 amThanks,that is very useful for me.
October 22nd, 2010 at 8:23 pmThank you Ajay for this nice little macro!
November 16th, 2010 at 7:36 amI has saved me a lot of time when implemented into mine macro.
The align right patch you wrote for Roger works fine btw
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.
December 10th, 2010 at 6:22 amThis 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!
December 14th, 2010 at 3:11 pmThanks 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?
January 5th, 2011 at 4:26 pmThat´s great¡¡¡
January 20th, 2011 at 12:21 pmThank you! Just what I was looking for.
Dan
January 24th, 2011 at 11:52 amMadison, WI
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,
February 16th, 2011 at 6:54 pmVicki
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?
February 28th, 2011 at 8:41 pmGreat stuff, saved me weeks 🙂
March 31st, 2011 at 9:47 amThis 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?
April 7th, 2011 at 12:31 pmAwesome code, and about one tenth the length we used with a “string & fill” method.
July 22nd, 2011 at 12:05 pmYou saved me a ton of work. You’re a master!! Thanks for putting this tool together.
September 21st, 2011 at 10:59 amPLEASE, i DONT SEE THIS ‘Export to Fixed Length Format’ button HERE?
February 27th, 2012 at 6:56 amHi, thanks a lot for sharing this. Just what I needed… THANKS!!
May 4th, 2012 at 11:11 amThis utility is fabulous!! It’s incredibly easy and saved MANY hours of headache!!
May 10th, 2012 at 10:34 amI don’t know you man, but you rock !!! Thank you very much for this macro.
June 20th, 2012 at 8:42 amgreat Thank you
October 3rd, 2012 at 2:32 pmGreat 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?
February 12th, 2013 at 9:29 amThanks for your efforts Ajay! works nicely!
April 9th, 2013 at 10:02 amThanks alot Ajay! Right fixed works nice.
June 5th, 2013 at 5:15 amHello, 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?
July 2nd, 2013 at 9:56 amIt 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.
July 6th, 2014 at 1:09 amThis 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*
March 6th, 2015 at 11:53 amAwasome !! this works. Thank you very much.
November 24th, 2015 at 4:15 pmJust 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?
THANK YOU!!
March 2nd, 2016 at 1:00 amThis was super helpful for a last minute report I had to file in a very specific format. What a legend for posting this.
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?
January 22nd, 2017 at 4:22 pmThanks
Patrick
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.
Thanks
January 23rd, 2017 at 4:59 amPatrick
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
July 13th, 2017 at 8:16 pmWow! 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!
August 15th, 2019 at 12:10 amHi, 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!
Cheers,
August 11th, 2020 at 11:46 amRalf
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.
September 3rd, 2020 at 3:40 pm