Controling space when exporting EXCEL to text.

We have a program that reads files in a fixed format style of FORTRAN.  8 characters per column of data with no delimiter.  I want to export a text file from EXCEL such that each column is limited to 8 characters, that it will stuff blanks as fillers.  In somecase it will be a fixed number (i.e. 1.2) and other it will be an exponent (i.e. 210E09)

Basically, I need to control the text output of Excel like the good old FORTRAN days.
0
anonymous (74722)
6/4/2004 12:26:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
517 Views

Similar Articles

[PageSpeed] 0

I found that when I use this technique, it goes a little smoother if I choose a
nice non-proportional font (I use Courier New) so I can see how things look).

But another way is to use a helper cell where you concatenate your values.

=RIGHT(REPT(" ",8)&TEXT(A1,"0.0"),8) & RIGHT(REPT(" ",8)&TEXT(B1,"0E+00"),8)

(modify your formatting to match)

Then copy that column and paste into notepad and save the text file from there.

I also add a cell like this near the top of that column:
=REPT("----+----|",10)
And format the whole column as Courier New

(If I have to do it lots, I'll leave the formulas and hide the column!)

Mr Smith wrote:
> 
> If you want to create a fixed width (space delimited) text file the easiest way is to adjust the column width in your file to 8 (Format, Column, Width from the menu) then save you sheet as "Formatted Test (Space Delimited).*prn"
> 
> The problem I have found with this method is that if your records are very long it tends to wrap the text round.
> 
> I think the way to get round this is to use a macro to fill the cells with " " spaces and export as a regular text file, although I need to test this. Hope this helps.
> 
>      ----- FoMoCo-Tom wrote: -----
> 
>      We have a program that reads files in a fixed format style of FORTRAN.  8 characters per column of data with no delimiter.  I want to export a text file from EXCEL such that each column is limited to 8 characters, that it will stuff blanks as fillers.  In somecase it will be a fixed number (i.e. 1.2) and other it will be an exponent (i.e. 210E09).
> 
>      Basically, I need to control the text output of Excel like the good old FORTRAN days.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/5/2004 12:43:12 AM
Thank you both.  Much appreciated.
0
anonymous (74722)
6/8/2004 12:56:06 PM
Reply:

Similar Artilces:

Text alignment using ESC sequence
I am trying to print receipt using OPOS. I want to print Items on the left and the prices on the right. I tried the followings, but they don't work... printNormal "Item 1" + ESC|rA + "123.45" printNormal ESC|N + "Item 1" + ESC|rA + "123.45" printNormal ESC|N + "Item 1" + CR + ESC|rA + "123.45" + ESC|N printNormal ESC|N + "Item 1" ; printNormal ESC|rA + "123.45" + ESC|N The printer always print 'everything' on the right. I tried with bold, and wide characters, and they behave as it suppose to be. T...

Why do cells in Excel print shorter than displayed on-screen?
When I type a lot of data in a given cell (textwrapping is on), the entire contents of the cell display correctly. But when I print the worksheet, the cell height appears shorter -- truncating my text. What's up with that? And how do I fix it without adjusting every cell manually (which screws up my display)? ...

locking a text box in a subform
Hi there, I've got a form that looks like this: MAIN FORM SUBFORM1 SUBFORM2 SUBFORM3 I'm trying to code an event in a listbox in SUBFORM1 that locks a textbox in SUBFORM2 if the user picks a certain item from the list. However, I can't seem to get the code right in order to do it. I tried this: If whatever Then Forms!subfrm1.subfrm2!Listboxname.Locked = True End if However it keeps telling me it can't find subfrm1. Any ideas? jjmclell On Aug 22, 8:55 am, jjmcl...@lakeheadu.ca wrote: > Hi there, > > I've got a form that looks like this:...

PCI Simple Communications Controller
I'm running XP Pro SP3, and had a scary start-up this am. System found "new hardware" - PCI Simple Communications Controller, I was unable to connect to Internet, and anti-virus start-up scan locked up. I had no problems accessing hard drive files or running installed s/w. After about 3 reboots, everything seems fine, scans found no suspicious files. I took a look at Device Manager and everything looked good, but I was unable to find anything specifically related to PCI Simple Communications Controller. I Googled this Controller, and while I found numerous referenc...

Calculating age from two date fields-Excel 2003
I'm not sure what section this question should go in. I need to find if a person was 40 years of age or older (= or >) at his Date of Hire. I have two columns, Birth Date and Date of Hire. How would I write a formula to give me this answer? Thanks in advance Try this: A1 = DOB B1 = DOH =DATEDIF(A1,B1,"y")>=40 That will return either TRUE or FALSE -- Biff Microsoft Excel MVP "Veteran of the Software Cycles" <VeteranoftheSoftwareCycles@discussions.microsoft.com> wrote in message news:97B155B5-68F0-49EB-910C-549B84737765@microsoft.com... > I...

Advanced filter poor performance in Excel 2003
I have an advanced filter executed in VBA that worked great until upgrading to Excel 2003. When I put in criteria the first time, the filtering works fine, but when the criteria row is blanked out so that all rows are unhidden, it takes forever for that to happen. I suspect that every formula on the sheet is being recalculated. Is there a technical explanation for why this problem happens now? Is there a way to work around this without setting the sheet calculation option to manual? I've seen slow downs when filtering, but usually showing all the rows is quicker--well at...

excel 2002
Hi folks, Have a bit of a problem. My company has a daily report using excel where we copy/paste info from an external source into it. when we were using 97, the growth of the file was fine(would go from a 1meg file to a 1.5meg file most times), but now that we're using the 2002 version, the files sizes are getting pretty silly(again from a 1meg file up to a 3-3.5 meg file.) After some testing, it is because of the four worksheets we paste into. We paste into it, run a macro to move that info into the places we want it, then clear the page....yet those pages are retaining the inf...

Keep count of rows in Excel
I am trying to print row count on my output regardless of the size of the worksheet or the number of rows. Any ideas? Experiment with this; it may offer a solution. You can go into the print setup and have it print the value of this cell in the header or footer or whatever. =COUNTA(A5:A8) Counts the number of nonblank cells in the last 4 rows of the list (4) >-----Original Message----- >I am trying to print row count on my output regardless of >the size of the worksheet or the number of rows. Any >ideas? >. > ...

Exporting Access into Excel workbook
Is there a way to export an access report into excel workbook where data is broken out onto multiple spreadsheets? Please help. Thank you!! In the absence of another reply ..... I think not. In my opinion you would be better off exporting sets of data from th *original Access table* to Excel - or using Data/Get External Data i Excel. I am currently experimenting with a similar job where it is necessar to have an Access table containing worksheet names linked to the mai table to enable this. I am setting up an Excel macro to import t worksheets. It may be quicker to use an Access Make T...

Cannot copy table from Excel 2007 to Word 2007 w invisible grid li
How do I copy a table from Excel 2007 to Word 2007 with invisible grid lines? In previous version of Word, it was possible to do so by clicking on "insert special" and choose one of the categories (I have forgotten which). With Word 2007, the grid lines become visible no matter which option I choose under "insert special". Is it possible for you to help me? Thanks! To hide the gridlines, click the View Gridlines button (to deselect the option) on the Table Tools Layout tab. This setting will be preserved between Word sessions. If you are in fact referr...

Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a line chart from a data table, then move the plot points on the chart to "smooth" the line, and the source data chart value would change with the corresponding new plotted point. I'd like to use that capability with Excel 2007 but can't find to activate it. Any power users know how? Thanks! Ed Ed, Microsoft decided that users didn't need that feature. More than likely, they were tired of support calls asking why the values changed when one dragged a data point...... No really, they took i...

How do I put a picture in excel with out using insert?
I'm trying to install a photo in an excel spread sheet, but I do not want it showing all of the time. I would like to have it in a cell, which when the cell is highlighted it appears, or some thing to that effect. Maybe this is an idea: http://www.geocities.com/smplprgrsrc/files/AutoPicture.zip "RFCahoon" <RFCahoon@discussions.microsoft.com> schreef in bericht news:65776443-0B28-4F6C-B0CE-899DEA1D478A@microsoft.com... > I'm trying to install a photo in an excel spread sheet, but I do not want > it > showing all of the time. I would like to have it in a...

Text boxes move on their own
Help! I have a frustrating situation. What causes text boxes to move around on charts on their own? For instance, I have a group of shapes including some text boxes that reference information on sheet such as messages or summary numbers. Sometimes when the data change for a chart, the boxes move. I've turned off Resize with chart and I've "moved to front" but I can't seem to figure out what causes these changes. Another example is a dynamic subtitle placed directly under the title in the chart. When inserting it the first time, I center justify the text in the box,...

How to import an excel document (form) into publisher
I have tried several times to import a form I produced in excell but every time I try it shuts down my publisher document I am importing to. Which versions of the software? What steps are you taking? -- JoAnn Paules MVP Microsoft [Publisher] "Warren" <Warren@discussions.microsoft.com> wrote in message news:DBC5D466-CE90-4657-BDFD-577984D46723@microsoft.com... >I have tried several times to import a form I produced in excell but every > time I try it shuts down my publisher document I am importing to. Have you tried: 1. Copy > Paste from Excel to Publisher...

Where is Excel.h? #2
I am having a problem finding Excel.h. I understand the file is created when you follow the instructions in the knowledgebase article. I am running Visual Studio 2008. Mot MFC 6.0 So, I cannot generate the files. Do I need to install C++6.0, just to get the files Excel.h and Excel.cpp? Any advice/ suggestions would be appreciated. I see the same question was asked a couple of years ago, and I see that the individual found a solution, but he does not actually say that the solution was to generate the files under C++ 6.0. Thank you. Which knowledgebase article? AliR. "Cameron_C&qu...

Excel 97, strange rounding
I am using state of the art excel 97 at work. When I type in a number "4" it comes up as .04, when I put in "400" I get 4. First thing I thought was precentage, ok so I changed it to number format with 2 decimal places and that resolved nothing. Nothing I do to the cells changes this. Ideas? Someone has set your Excel up for quick money entry - you can type 12345 to get 123.45 (one whole keystroke saved) To undo this: Tools | Options | open the Edit tab | uncheck the Fixed Decimal Places box best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/blie...

Using variables in Excel 2000
Yeh, yeh, I know for some of you guys this is prbably a very simple task. This is what I am wanting to do Assign a list of variables(in this case they are a two letter designator) at the top left of the worksheet and the cell to the right of the cell input the value for the variable In a different row and colum use any of these varaibles and be able to change the values at the top list. I also need to be able to just insert a new variable in "different row and colum" and have it use this new variable Sincerely yours Le Les The name you type in the first column will only be for...

Excel Crashes when trying to open a User Form
When I open the workbook and click on the worksheet to open the user form excels stops working. I get Microsoft Office Excel has stopped working and is trying find a solution, but it never does. I have saved it in both 2007 & 2003 versions and both get the same problem. What is odd is if you open the Userform Workbook, then open the VBA code window, both userform versions run fine, but if you do not open the Code window Excel locks up everytime. Here is the code. Sheet 2 Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub ...

Bug ??
Hi In Excel 2007, I am highlighting two columns of data (%) and creating a new Conditional Formatting Rule. I create a traffic light format with green > 80%, amber between 75 and 80 percent and the rest as red. HOWEVER I then get inconsistent formatting - ie it seems to be ignoring the 80% cutoff. Of the data, I get green for 89,90,98 - anything >= 88%, amber for 86,87, then red for anything else. If I go into Manage Rules, Edit Rule - the thresholds are correct. Can anyone help me here? This is driving me mad. George Hi George Try using 0.8 rather than 80% as your...

Paint problem with child dialogs and tab control
Hi, I have a main dialog that has a tab control. For each tab, I have a child dialog. The problem is that the tab control's color isn't solid (with XP styles), so if I use COLOR_WINDOW for the child window, it will not take the gradient pattern of the tab control. How can I manage to make this work? Thanks! :) U�ytkownik "Luc" <l_vandal@hotmail.com> napisa� w wiadomo�ci news:yKEnb.2971$Nm6.194226@news20.bellglobal.com... > Hi, > > I have a main dialog that has a tab control. For each tab, I have a child > dialog. The problem is that the tab control...

MFC: adding a control into a dialog window
void ChelloDlg::OnBnClickedEnable() { CWnd *parentWnd = GetDlgItem(IDD_HELLO_DIALOG); CStatic * newlabel = new CStatic(); newlabel->Create(_T("Hello World"), WS_CHILD | WS_VISIBLE, CRect(10, 10, 100, 100), parentWnd); } I am using a button ID_ENABLE to trigger the adding of label into IDD_HELLO_DIALOG. The program would crash ("Debug Assertion Failed") when I hit the button. What am I missing? -- @~@ Might, Courage, Vision, SINCERITY. / v \ Simplicity is Beauty! May the Force and Farce be with you! /( _ )\ (Xubuntu 8.04.1...

Help on how to use Offset in Excel
Can anyone explain how OFFSET works in Excel? I'm trying to find out how to use this in order to create a sports league table for my school coursework. Can anyone help please? Thanks in advance. -- petros89 ------------------------------------------------------------------------ petros89's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24645 View this thread: http://www.excelforum.com/showthread.php?threadid=474047 Excel's Help is generally quite informative about what functions will do. What are you not finding clear about Help for OFFSET ? Jerry ...

How do I get menus back when I right click on an Excel Spreadshee.
I have a computer that when I right click on a spreadsheet tab I do not get the normal menu to pop up. What do I need to do to get the menus back and functional??? Try Application.commandbars("cell").enabled=true in the immediate window -- HTH RP "David G" <David G@discussions.microsoft.com> wrote in message news:74ED0D6B-958E-40E5-ACDC-2F46B98BC1FC@microsoft.com... > I have a computer that when I right click on a spreadsheet tab I do not get > the normal menu to pop up. What do I need to do to get the menus back and > functional??? hi Right clic...

Set focus to textbox after spin-button control is pressed??
My userform contains one Textbox and one vertical Spinbutton control. If someone presses either the spin-up or spin-down button, I want the input focus to immediately change from the spinbutton to the textbox control. I tried using the code below: Private Sub SpinButton1_SpinDown() UserForm1.TextBox1.SetFocus End Sub Private Sub SpinButton1_SpinUp() UserForm1.TextBox1.SetFocus End Sub I thought the code above would be sufficient, but sometimes when I press either spin button the focus does not change immediately to the textbox control. It seems like the user has...

control arrays
I'm using VBA with Excel 5 and developing a form. Under VB6 I'd create a control array to allow me to access 10 text boxes using a loop. VBA doesn't appear to allow this. Is there any way to simulate a control array eg by programatically modifying the names of the text boxes? Thanks I never worked on Excel 5, but I do use VBA Collection for loading the different objects. You can load textboxes in a collection and reuse the collection. However, I don't think VBA allows to change the names on runtime. (Please correct me if I am wrong). -- Thanks, Vikas ...