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.
7 Answers
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.
4If 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.
3Wow, 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 SubFirst, format your data as Courier New (or some other fixed width font). Then save as .prn and you'll get true fixed width.
1Expanding on Jeff Atwood's answer, since it would not let me comment there:
I modified his macro to set the column width to the widest cell in that column and to have each column its own width. His macro only found the widest cell in the first row and then set the width of all columns to it.
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, linesize As Long Dim selectedrows As Integer, selectedcols As Integer Dim arr arr = ActiveSheet.UsedRange selectedrows = UBound(arr, 1) selectedcols = UBound(arr, 2) ReDim CellSizes(1 To selectedcols, 2) As Integer Dim temp As Integer Dim cellsize As Integer linesize = 0 For c = 1 To selectedcols cellsize = 0 For r = 1 To selectedrows temp = Len(CStr(Cells(r, c))) If temp > cellsize Then cellsize = temp End If Next CellSizes(c, 0) = cellsize + 1 CellSizes(c, 1) = linesize linesize = linesize + cellsize + 1 Next c Dim line As String Dim output As String For r = 1 To selectedrows line = Space(linesize) For c = 1 To selectedcols Mid(line, CellSizes(c, 1) + 1, CellSizes(c, 0)) = 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 SubThis is a killer for me. It has a few options as well.
4It works with Access out of the box: with this way I managaed it quite easy and fast - better than with Excel. In my case it was a conversion of the table.
1
