Excel 2003: Alignment problems saving workbook as prn file.

[Ryan]

This seems to be a very specific problem so I will give exact steps on how to
reproduce the problem.

1. Open a new blank xls
2. Make sure error checking is on and specifically the option for "Numbers
stored as text." (Tools, Options, Error Checking)
2. Format columns A-C as Text
3. Right-Align column B - (this is key)
4. Enter 1234 in cell A1
5. Enter 5678 in cell A2
6. Enter 9999 in cell C1 and notice with each number entered the green
triangle appears indicating error checking.
7. Enter 1234 in cell D1
8. Now format cell D1 as Text and notice no error checking even though it is
number stored as text.
9. Copy cell D1 to cell B2 and still no green triangle
10. Save the file as Formatted Text (Space Delimited) (*.prn)
11. Now open the .prn file in NotePad and notice how column B is not aligned.
12. It seems that cells that are right-aligned and have the error checking
triangle will be shifted to the left one space so that they are not aligned
anymore.
13. Using the formula =cell("type",B1) shows that the cell with error
checking is "l" which means it contains a text constant and =cell("type",B2)
is "v".
14. Obviously they are treated differently which you can even see opening
the .xls in NotePad. Cells with error checking internally are stored
different than the cells without the error checking triangle.
15. However, both cells look the same in Excel and should therefore save
the same when saved as a prn file.
16. One fix is to highlight column B after the paste and then click Data,
Text to Columns, Finish.
17. Is this situation known and/or is there a fix?

Ryan

 

[Nick Hodge]

Ryan

I'm sorry, I can't replicate your example. These are summations by me...not
fact

1) Smart tags seem to represent 'errors' information that the user may not
know, therefore a pre-formatted cell, column would show a text number error,
but they don't appear to be 'retrospective', that is if you enter a number
and then convert it to text as in D1 it shows no smart tag. I don't find
this an issue as you have knowingly made the number in the cell text. Maybe
a calc should show it, or 'resetting ignored errors' should maybe do so...it
doesn't, but the functionality you seek me be someone else's annoyance.

2) The .prn file structure (Fixed width - Space delimited) is determined by
column width, that is, if you want your 'fixed width' to be 4 characters,
then set it to that, my default width is 8.43, which translates to 8
characters, my data is

1234....1234....1234....1234

Where the periods represent spaces so

Right aligned, 4 spaces, right aligned, 4 spaces, right aligned, 4 spaces, 4
chars (EOR) No spaces after end of record because it is the last field and
numeric.

I see no issue (Excel 2003 SP1), but maybe I am misunderstanding.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Ryan]

You need to follow my detailed steps exaclty, I believe everything is there,
to see what I am talking about.

Only one column should be right-aligned. In that column cells that have
error checking showing with the green triangle, when saved as a prn file will
be off by a space when opened in Notepad.

Ryan