Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Special formatting for output ('97/SR1)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

    You might try this in cell B1:

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

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

    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.

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

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

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

    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>

    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

    Right on, Legare!

    Thanks alot.

  8. #8
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Special formatting for output ('97/SR1)

    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

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Special formatting for output ('97/SR1)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Lounger
    Join Date
    Feb 2001
    Location
    Middletown, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format_text ('97/SR2 and 2000)

    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?

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format_text ('97/SR2 and 2000)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •