Insert/Append spaces to end of Excel row

[Chopstik]

I have a text file that I have to modify in Excel (in order to renumber the rows after some were removed). I then have to resave the file back to space-formatted text so that each row is 186 characters long. However, my original file somehow truncated so that it is only as long as the actual text (which is 166 characters) and I would like to add 20 blank spaces to the end of the file in Excel. However, if I attempt to add 20 spaces and then save as a .prn file, it doesn't seem to recognize the spaces in the file.

I also tried to increase the width of the last column by 20 spaces (or whatever the pixel equivalent is) - same as I had to do for some of the inner columns - but this also does not work when saving. Not sure what else to try and Excel isn't my strong suit. Can anyone offer any suggestions or insight? Thanks.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[itfellow]

Hey Chopstik,

Did the lines of text in your original file get truncated when you opened it in Excel, or were they already truncated before you opened it in Excel? If opening it in Excel is what performed the truncation, I think you might be able to avoid that by opening the text file in Excel and specifying "text" formatting for each column before finishing the text file import wizard. If the columns are formatted only as "general", I have seen it drop off white spaces, but if they are formatted as text, I believe it keeps the white spaces.

If you still need to add the space characters, the only way I can think of is a little clumsy, but it would work: insert 2 columns next to the one you are trying to add spaces to. In the first column, type the formula

=a1&" "

where a1 is whatever cell you are trying to add spaces to. Then copy the cell with the formula into the entire (new) column. Then copy the whole new column and paste it into the second new column using "paste special" and "values". You will then have a column with just text of your original text plus the spaces. You can then delete your original column and the first new column.

 

[Chopstik]

itfellow,

Thanks for your response and suggestions. I had already tried both of your suggestions and both failed to resolve the problem. What I finally did was to manipulate the file in Excel and then import it into Access. From Access, I was able to create an export template (using the wizard) in order to properly generate the formatted text file.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[xlbo]

=A1 & rept(" ",186-len(A1))

would be a formula to generate the extra spaces

You could then copy / paste values and save as prn - but that is just an alternative as you seem to have found a working solution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question