How to create fixed lengh cell and rows in Microsoft Excel

[Henriksen]

Need fixed length cells and rows in excel spreadsheet saved as space delimited .prn file. The .prn file will be FTP'd to Mainframe computer file (which has fixed length fields (each cell = a field in Mfrm file) in a fixed length record. Is there a way to format cells as text data, with a fixed number of spaces in the cell. Then the client could overtype the spaces with the required data.

[Alfons]

The output file won't be space delimited--it'll be fixed width. There's a limit of 240 characters per line when you save as .prn files. So if your data wouldn't create a record that was longer than 240 characters, you can save the file as .prn. I like to use a fixed width font (courier new) and adjust the column widths manually. But this can take a while to get it perfect. (Save it, check the output in a text editor, back to excel, adjust, save, and recheck in that text editor. Lather, rinse, and repeat!). Alternatively, you could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00"). (You'll have to modify it to match what you want.). Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro.

[Gania]

There is a macro for columns. You can try the below code and then check back.

 Sub colonnesEnCentimetres () 
 Sun cm As Integer, points As Integer, savewidth As Integer 
 Sun count As Integer 
 Application.ScreenUpdating = False 
 Application.InputBox cm = ("enter the column width in cms"  "The desired column width", Type: = 1) 
 If cm = False Then Exit Sub 
 = Application.CentimetersToPoints points (cm) 
 savewidth = ActiveCell.ColumnWidth 
 = 255 ActiveCell.ColumnWidth 
 If points> Then ActiveCell.Width 
 MsgBox "width" & cm & "is too broad" & Chr (10) & _ 
 "The maximum value is" & _ 
 Format (ActiveCell.Width / 28.3464566929134, _ "0.00"), vbOKOnly + vbExclamation, "Invalid width" 
 ActiveCell.ColumnWidth = savewidth 
 Exit Sub 
 End If 
 lowerwidth = 0 
 = 255 upwidth 
 ActiveCell.ColumnWidth = 127.5 
 curwidth = ActiveCell.ColumnWidth 
 count = 0 
 While (ActiveCell.Width <> points) And (count <20) 
 If ActiveCell.Width <points Then 
 lowerwidth = curwidth 
 Selection.ColumnWidth = (+ curwidth upwidth) / 2 
 Else 
 upwidth = curwidth 
 Selection.ColumnWidth = (+ curwidth lowerwidth) / 2 
 End If 
 curwidth = ActiveCell.ColumnWidth 
 count = count + 1 
 Wend 
 End Sub