Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Text changes to Formula (2003 sp3)

    I have a text file, a report, which I need to load into Excel to do other work; the file comes from another source outside our organization.

    One column contains symbols for the days of the week, such as MTWRFSU, with a dash as a substitution variable for a missing day. An event which occurs on Monday, Wednesday, and Friday appears as M-W-F-- while something happening only on Saturday and Sunday is -----SU. Most of the time the Excel column treats this information as text but, when the event occurs on Sunday (with or without a prior day), the cell is treated as a formula (=------U instead of ------U).

    Can someone tell me why this is happening and what we can do to avoid this? We are just opening the file with Excel; when we import the file into Excel, we have the opportunity to select TEXT for the format of the field and all is well.

    Thanks to all!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text changes to Formula (2003 sp3)

    It does this because there are at least 3 ways to start a formula in Excel using the keyboard. Formulas can be started with either a "=", a "+" or a "-" . When you open the file as opposed to importing the data, Excel looks for the -,+, or = and converts the cells into formulas.

  3. #3
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Text changes to Formula (2003 sp3)

    I understand what you're saying but there is still something missing. When an event occurs on Monday, Wednesday, and Sunday, it comes out as M-W---U which does not throw an error. Event happening on Friday, Saturday, and Sunday, appear as ----FSU and throw an errors. Events occurring on Tuesdays and Thursdays come out as -T-R--- and do not throw an error.

    The problem seems to be the - as character 1 and U as character 7 with or without other characters having their letter designations instead of hyphens.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text changes to Formula (2003 sp3)

    It sounds like you have already solved it. Import as text....

    Another option is to change the text strings in your file to start with a single quote ('), this should not trigger the string to be considered a formula.

    Steve

  5. #5
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Text changes to Formula (2003 sp3)

    Thanks! The idea of starting the string with a ' would do the trick but I still don't know the why . . .

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text changes to Formula (2003 sp3)

    It is as Mike indicated, the minus (-) triggers excel to believe it is a formula which when put into cell will give a #Name error...

    Steve

  7. #7
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Text changes to Formula (2003 sp3)

    But why does it not throw an error for events like --WR--- ?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text changes to Formula (2003 sp3)

    It seems to me that if it starts with a "formula indicator" [minus (-), plus(+) or an equal (=) sign] and ends with some of the symbols (eg: any of =+-/><,'";:|]}[{)(*&^$#@!~), it somehow knows that it is not a formula. While staring with the indicator and ending things like: letters, numbers, %, _, , ., ? will make excel think it is part of a formula. I don't know the routine it uses...

    Thus, if you ended in "U" it would be treated as a formula...

    Steve

  9. #9
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Text changes to Formula (2003 sp3)

    OHHHH!!!! NOW I understand! Thanks! This sure was mystifying me.

Posting Permissions

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