Special formatting for output ('97/SR1)

[Hanan]

Hi,

I have pasted some data into my excel worksheet for analysis. The data came in a fixed format as follows:

1 1 3 .00000E+00 .00000E+00 .00000E+00 1.73071E+06 7.19820E+02 3.30813E+01 1.90435E+05
2 1 3 .00000E+00 .00000E+00 .00000E+00 1.73061E+06 7.19820E+02 4.33058E+01 2.73185E+05
2 4 3 4.90893E+00 1.59851E+01 -1.59995E+01 1.73058E+06 7.19820E+02 2.35627E+02 4.08802E+05
3 4 3 8.62909E+00 2.70697E+01 -2.33887E+01 1.73058E+06 7.19820E+02 3.67840E+02 6.91837E+05

That is, I7,I7,I7,R13,R13,R13,R13,R13,R13,R13. Where I indicates spaces assigned to integer number, and R indicates spaces assigned to real (non-integer) number. You might recognize this format as one from a FORTRAN code.

After I make some adjustment to the data, I copy and paste it back to the original file that contained the above data. However, when I do that I get the wrong format. So, I have used the following excel equation to adjust for it:

=" "&PRO_file!A6&" "&PRO_file!B6&" "&PRO_file!C6&" "&TEXT(PRO_file!N6,$B$1)&" "&TEXT(PRO_file!O6,$B$1)&" "&TEXT(PRO_file!P6,$B$1)&" "&TEXT(PRO_file!G6,$B$1)&" "&TEXT(PRO_file!H6,$B$1)&" "&TEXT(PRO_file!I6,$B$1)&" "&TEXT(PRO_file!J6,$B$1)

Where $B$1 is the number format 0.00000E+00.

This equation works for the top 2 lines of the data above (where all numbers are positive). But fails where the number becomes negative, such as in the bottom 2 lines of the above data.

Is there anything in excel to adjust for that?

Thanks in advance. Hanan.

By the way, I am dealing with close to 2000 lines of data.

[LegareColeman]

You might try this in cell B1:

[pre[]
="0.00000E+00;-0.00000E+00"
[/pre]

[Hanan]

Thanks for the quick response.

I tried your suggestion, Legare. But I still have the same problem. I am trying to save 13 spaces for real numbers, ie,

what I want: _-5.11111E+01; what I get: __-5.11111E+01

where "_" represent an empty space (1 character)

For instance,

______1______1______1 contains 6 spaces between integers, and these spaces will not be conserverd when
I have the following: ______1_____22____333
The only thing that is conserved is the spaces assign the the integers. In this case 7 spaces.

Any ideas?

Hanan.

[Bithmus]

What about doing a paste special -> value. That way it wouldn't take the formatting with it.

[Hanan]

But I do need the formatting in order to paste in the correct format into the original data.

[LegareColeman]

Ah, now I see what your problem is. Thy changing each of the Text functions for the real numbers to:

<pre>RIGHT(TEXT(PRO_file!N6,$B$1),13)
</pre>

[Hanan]

Right on, Legare!

Thanks alot.

[mhsherif]

What does it mean to have the number format as $B$1? Does this mean that the number format is the same as the format of cell $B$1 or does it mean that the format is entered in cell $B$1 as a string?
Thanks

[SammyB]

The second: "the format is entered in cell $B$1 as a string," in this case B1 contained '0.00000E+00 (the single quote entered it as text). HTH --Sam

[mhsherif]

To use the text function, one must specify the format as the Format_text.
In Excel 97 SR2, the help function indicates that
Format_text is a number format in text form from the Category box on the Number tab in the Format Cells dialog box
(not General).

In excel 2000 it says
Format_text is a number format in text form from in the Category box on the Number tab in the Format Cells dialog
box. Format_text cannot contain an asterisk (*) and cannot be the General number format

The question is where to get the number format from and how does it matter?

Text(q10,q10) seems to work sometimes. Q10 is of the general format.

Try the following experiment

Enter 3 in the cell A1 and set A1 to general format
Then set B1="amount " & text(A1,A1) B1 shows "amount 3" w/o the quotes.
Change the format of A1 to 2 decimal places; A1 shows 3.00; B1 does not change
Set C1="amount " & text(A1,A1) and C1 shows "amount 3" w/o the quotes
Change the format of A1 to currency so it shows $3.00; B1 and C1 do not change
Change format of A1 to percentage so it will show 300%; B1 and C1 do not not change

Is there a rational explanation for this?

[SammyB]

I agree that there does not seem to be a rational explanation for this behavior: they should all give some sort of error because "3" is not a valid number format. The second parameter to the text function tells how to format the number, for example "0.00" says to format it with two decimal places. These codes are more commonly referred to as custom number formats because they are also used in the Format>Cells>Number tab>Custom category. It is difficult to find a list of these codes in the help file: I always hit a couple dead-ends before finding them. In XL2K, use the answer wizard for "custom number formats" and choose "Create a custom number format." HTH --Sam