General discussion

Locked

Excel to text

By john.a.wills ·
I have a 5-column Excel file. I want to convert it to 4 fixed-width fields in a text file. The first 2 columns do not get transferred. The next is a SSN with hyphens; I want it 9(9). The next is a dollar amount which I want in 9(9).99. Then comes a single character which I want in XX. The last field required is a constant, the year, in 9999 format.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by dryflies In reply to Excel to text

First special paste (values) the data you want to another sheet in the workbook without the two columns you want to skip. next, do a search and replace on the hyphens in the SSN to eliminate them, the year can be put in using =YEAR(NOW()) or YEAR(b1) where B1 is the column with the date whose year you want. the dollar amount can be formated using "format cells" use a custom format of $00.00 to keep it 6 characters wide (4 nums,the period and the $), then save the spreadsheet as a .prn file (space delimited) or print it to a text file.
please rate my answer

Collapse -

by john.a.wills In reply to

It's helping quite a bit, actually. I do not know how to propagate the year down the whole column. After that I can try the printing.

Collapse -

by dryflies In reply to Excel to text

put the formula into one cell, when you select that cell, its border will be in bold and a small square will appear in the lower right corner of that border. drag that small square down the column to propogate the formula.
I have given you more than 650 pts of good advice. you should accept the answer. if you need more help you can ask again. or contact me directly.

Collapse -

by john.a.wills In reply to

All done now

Collapse -

by john.a.wills In reply to Excel to text

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums