Software

Ask something
Discussion Locked

Excel to text

0 helpful
5 replies

by john.a.wills

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

5 replies

by dryflies

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

by john.a.wills

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.

by dryflies

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.

This discussion is closed to new replies.