Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Excel has the functionality to import fixed-width text files where it presents a dialog that lets you choose where the begins and ends of fields are which it puts into columns.

Does it also have functionality where, given an existing spreadsheet, you can export to a fixed-width text file?

If so, how do I access this? I have tried using Save As and choosing Text File but it seems to only save as Tab-delimited which doesn't help me.

This is Excel 2003 if it matters.

share|improve this question

5 Answers 5

up vote 14 down vote accepted

I think the closest you can get from native Excel functionality is Save As | Formatted Text (Space Delimited) (*.prn). It will automatically determine the widths and insert spaces to pad to that width as necessary.

Beyond that you need to have a macro or other add-in that will let you do more.

share|improve this answer
    
Just to add some clarity - it will "automatically" determine widths based on the width you specify for your columns. While normally the measurements approximate to ems (as far as I recall), when exporting to fixed width prn they are taken literally and rounded down to give integer values used for the number of characters in a column of the prn file. Spaces are added for padding, to the left or right depending on alignment in the cells (so by default numbers wil be padded on the left to force them right, text on the right to force left, but if you have changed alignments they will be different) –  AdamV Jan 25 '10 at 21:55
    
This does do what I was looking for. Thanks! –  RationalGeek Jan 26 '10 at 19:34
    
Please note: Formatted text (.prn) is limited to 240 characters per line in Excel support.microsoft.com/kb/249885 –  schultkl Sep 20 '12 at 16:57

If you have Office Professional, you can open your Excel file in Access, and then Export from Access. Access will let you specify a fixed-width layout for your exported file, and gives you extremely granular controls for specifying those widths.

share|improve this answer
    
While Access might do it, so will Excel which is what was asked for. –  AdamV Jan 25 '10 at 21:56
3  
Thanks for the downvote, but I thought it was worth mentioning that Access has a full version of this feature. Excel doesn't let you specify column widths directly, Access does. Yes, you can get the right column widths in Excel by diddling around with the column widths in the worksheet, but it's a pain and as someone who has to create fixed width files on a regular basis it isn't worth the time. If you have Access (which many people do) there's a much better way. –  davidcl Jan 27 '10 at 23:44
    
Excellent; thank you for this alternative : o ) –  schultkl Sep 20 '12 at 17:00

Wow, I was going to ask this question myself but it was already asked. All Excel clipboard output is tab delimited by default. This is kind of annoying for "real" plain text output when you have a fixed width font but not necessarily tab delimiter support.

Anyway, I found and modified a small Excel Macro that will copy the currently selected region as a simple fixed-width columns ASCII table -- like so:

187712 201    37     0.18   
2525   580    149    0.25   
136829 137    43     0.31   

Here's the Macro code. To use it, make sure you enable the Developer tab in Excel Options if you are using Excel 2007 or later.

Sub CopySelectionToClipboardAsText()

   ' requires a reference to "Windows Forms 2.0 Object Library"
   ' add it via Tools / References; if it does not appear in the list
   ' manually add it as the path C:\Windows\System32\FM20.dll

    Dim r As Long, c As Long
    Dim selectedrows As Integer, selectedcols As Integer

    Dim arr
    arr = ActiveSheet.UsedRange
    selectedrows = UBound(arr, 1)
    selectedcols = UBound(arr, 2)

    Dim temp As Integer
    Dim cellsize As Integer
    cellsize = 0
    For c = 1 To selectedcols
        temp = Len(CStr(Cells(1, c)))
        If temp > cellsize Then
            cellsize = temp
        End If
    Next c
    cellsize = cellsize + 1

    Dim line As String
    Dim output As String

    For r = 1 To selectedrows
        line = Space(selectedcols * cellsize)
        For c = 1 To selectedcols
            Mid(line, c * cellsize - cellsize + 1, cellsize) = Cells(r, c)
        Next c
        output = output + line + Chr(13) + Chr(10)
    Next r

    Dim MyData As MSForms.DataObject
    Set MyData = New DataObject
    MyData.SetText output
    MyData.PutInClipboard

    MsgBox "The current selection was formatted and copied to the clipboard"

End Sub
share|improve this answer

First, format your data as Courier New (or some other fixed width font). Then save as .prn and you'll get true fixed width.

share|improve this answer
    
This worked great for me. I also like to format everything as text before I do this. That way all data is left aligned when saved. –  Gary Brunton Jan 4 '13 at 20:17

"If you have Office Professional, you can open your Excel file in Access, and then Export from Access. Access will let you specify a fixed-width layout for your exported file, and gives you extremely granular controls for specifying those widths."

Just wanted to say that this worked great! Much better then fighting with excel to make it work. I work with fixed width files every so often and hate to think of the time I have wasted trying to get Excel to do this. I can even use this to edit a fixed width text file and have it retain everything if I use text field for all fields when importing the file. This makes me ask why is this functionality not built into Excel which is the normal place people would think to look for this?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.