Text changes to Formula (2003 sp3)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote