Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excel formatting to text file
hello
the problems is i have to save an excel file to text file, so that column A takes up the first 10 columns, column B takes up 7 columns in the text file and so on , or word it in another way each of the excel columns have to be placed at certain columns of the text file column a in excel takes up spaces 1to 7 in the text file column b in excel takes up 8-12 in the text file etc how would i go about doing this in excel 2003 thanks paul! |
#2
|
|||
|
|||
Save your workbook first!
One way is to save the file as a .prn file. I'd change the Font to Courier New so that you can widen the columns and how things look (and inspect the text file using Notepad to double check the .prn file). But there's a limit of 240 characters per line when you save as .prn files. One workaround... 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.) 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. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl ========= If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm paul wrote: hello the problems is i have to save an excel file to text file, so that column A takes up the first 10 columns, column B takes up 7 columns in the text file and so on , or word it in another way each of the excel columns have to be placed at certain columns of the text file column a in excel takes up spaces 1to 7 in the text file column b in excel takes up 8-12 in the text file etc how would i go about doing this in excel 2003 thanks paul! -- Dave Peterson |
#3
|
|||
|
|||
thats dave
that worked perfectly all the best "Dave Peterson" wrote: Save your workbook first! One way is to save the file as a .prn file. I'd change the Font to Courier New so that you can widen the columns and how things look (and inspect the text file using Notepad to double check the .prn file). But there's a limit of 240 characters per line when you save as .prn files. One workaround... 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.) 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. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl ========= If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm paul wrote: hello the problems is i have to save an excel file to text file, so that column A takes up the first 10 columns, column B takes up 7 columns in the text file and so on , or word it in another way each of the excel columns have to be placed at certain columns of the text file column a in excel takes up spaces 1to 7 in the text file column b in excel takes up 8-12 in the text file etc how would i go about doing this in excel 2003 thanks paul! -- Dave Peterson |
#4
|
|||
|
|||
thanks dave
that worked perfectly all the best "Dave Peterson" wrote: Save your workbook first! One way is to save the file as a .prn file. I'd change the Font to Courier New so that you can widen the columns and how things look (and inspect the text file using Notepad to double check the .prn file). But there's a limit of 240 characters per line when you save as .prn files. One workaround... 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.) 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. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl ========= If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm paul wrote: hello the problems is i have to save an excel file to text file, so that column A takes up the first 10 columns, column B takes up 7 columns in the text file and so on , or word it in another way each of the excel columns have to be placed at certain columns of the text file column a in excel takes up spaces 1to 7 in the text file column b in excel takes up 8-12 in the text file etc how would i go about doing this in excel 2003 thanks paul! -- Dave Peterson |
#5
|
|||
|
|||
Hi Paul,
Don't know if you will get exactly what you want but would suggest making a copy of the worksheet and using a fixed font and adjusting your column widths (Excel columns). Just a tad hard to read when you use the word columns, since Excel has Columns. Positions or "fixed width columns for export" would be easier to read. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "paul" wrote in message ... hello the problems is i have to save an excel file to text file, so that column A takes up the first 10 columns, column B takes up 7 columns in the text file and so on , or word it in another way each of the excel columns have to be placed at certain columns of the text file column a in excel takes up spaces 1to 7 in the text file column b in excel takes up 8-12 in the text file etc how would i go about doing this in excel 2003 thanks paul! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Combining & formatting cells with text - Excel 2002 | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |