Producing fix width text files
I want to export an excel file as a fixed width text file. I will have four
columns I want each to remain 10 charactors wide with the text left aligned and numbers right aligned. How do I export in this format ? Thanks Chris |
I'd use a helper column that would concatenate the values in the first for
cells: =LEFT(A1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(B1,"000.00"),10) &LEFT(C1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(D1,"000.0000"),10) (all one cell) Text in A1 and C1, numbers in B1 and D1. Then drag down the column. Then copy that column, paste into notepad and save my .txt file from there. Chris wrote: I want to export an excel file as a fixed width text file. I will have four columns I want each to remain 10 charactors wide with the text left aligned and numbers right aligned. How do I export in this format ? Thanks Chris -- Dave Peterson |
Thanks Dave this almost solves my problem one final little obstical remains,
this is I wan to maintain the 0's at the end of a number eg 5.010 this currently becomes 5.01. any ideas? "Dave Peterson" wrote: I'd use a helper column that would concatenate the values in the first for cells: =LEFT(A1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(B1,"000.00"),10) &LEFT(C1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(D1,"000.0000"),10) (all one cell) Text in A1 and C1, numbers in B1 and D1. Then drag down the column. Then copy that column, paste into notepad and save my .txt file from there. Chris wrote: I want to export an excel file as a fixed width text file. I will have four columns I want each to remain 10 charactors wide with the text left aligned and numbers right aligned. How do I export in this format ? Thanks Chris -- Dave Peterson |
This portion of the formula sets the format for numbers in column B:
&RIGHT(REPT(" ",10)&TEXT(B1,"000.00"),10) So maybe... &RIGHT(REPT(" ",10)&TEXT(B1,"0.000"),10) Would work ok. (or in the other portion for column D) Chris wrote: Thanks Dave this almost solves my problem one final little obstical remains, this is I wan to maintain the 0's at the end of a number eg 5.010 this currently becomes 5.01. any ideas? "Dave Peterson" wrote: I'd use a helper column that would concatenate the values in the first for cells: =LEFT(A1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(B1,"000.00"),10) &LEFT(C1&REPT(" ",10),10) &RIGHT(REPT(" ",10)&TEXT(D1,"000.0000"),10) (all one cell) Text in A1 and C1, numbers in B1 and D1. Then drag down the column. Then copy that column, paste into notepad and save my .txt file from there. Chris wrote: I want to export an excel file as a fixed width text file. I will have four columns I want each to remain 10 charactors wide with the text left aligned and numbers right aligned. How do I export in this format ? Thanks Chris -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 07:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com