Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to tell number of spaces between values in saved text file fromthe original xls file
Hi,
I want to save an excel xls file as the format of "formatted text (space delimited)". My question is given the excel file (from which, i know the width of each column, and the value of each range), how do I know the number of spaces between any two neighboring values in the saved text file? (I want to be very specific about that number) For example, if in the original excel file, range("A1") = "0.123" and range("B1") = "abc", what would be the number of spaces between the "0.123" and "abc" in the text file? For column width I can get in vba (columns(1).width), but I guess the number of spaces apparently also depends on the width of the specific content within a cell. For example, for a cell with the content "0.123" or "abc", the width of the content could be (the width of a single digit * 4 + the width of a decimal point) or (the width of a letter * 3). It should also depend on the font/format/size of that cell. After I know the width of the column and the width of the content, the number of spaces between neighboring values should be a function of the difference in the widths in excel. But I can't find any websites/books which would give me this kind of information. Thanks a lot, greatly appreciate any help or tips. Just to clarify, I am more interested in the answer to my question above, rather than a workaround such as saving the file first, then manually increase/decrease the spaces in the text file. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to tell number of spaces between values in saved text file fro
The answer to your question: Count the spaces
I save an excel file in PRN format ("formatted text space delimited) and found out that excel stripped all the control character out of the file. I had the text colored and no coloring ended up in the saved file. To make the data fixed width excel add spaces before each column and made the text right aligned. to find the width is then simple. first count the number of spaces. Then count the number of non-spaces. The total of the two count is the column width size. It is very simple to write a program that will extract these column widtths from the file. Just look at the first line of data in the file and perform the two counts I indicated above in a loop until you get to the end of the line. Put the results of each count in a worksheet so you can save the results. " wrote: Hi, I want to save an excel xls file as the format of "formatted text (space delimited)". My question is given the excel file (from which, i know the width of each column, and the value of each range), how do I know the number of spaces between any two neighboring values in the saved text file? (I want to be very specific about that number) For example, if in the original excel file, range("A1") = "0.123" and range("B1") = "abc", what would be the number of spaces between the "0.123" and "abc" in the text file? For column width I can get in vba (columns(1).width), but I guess the number of spaces apparently also depends on the width of the specific content within a cell. For example, for a cell with the content "0.123" or "abc", the width of the content could be (the width of a single digit * 4 + the width of a decimal point) or (the width of a letter * 3). It should also depend on the font/format/size of that cell. After I know the width of the column and the width of the content, the number of spaces between neighboring values should be a function of the difference in the widths in excel. But I can't find any websites/books which would give me this kind of information. Thanks a lot, greatly appreciate any help or tips. Just to clarify, I am more interested in the answer to my question above, rather than a workaround such as saving the file first, then manually increase/decrease the spaces in the text file. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Need Help Importing Text File Using Two or More Spaces as the Delimiter | Excel Discussion (Misc queries) | |||
auto save excel file every 10 minutes to its original file name | Excel Discussion (Misc queries) | |||
Saved ungrouped cells to a text file | Excel Discussion (Misc queries) | |||
Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) |