Excel converts text to formulas %@#$

I wish Microsoft stopped developing "helpful" programs. All their
"auto" stuff leads to nothing but problems. Here is an example:

I have to process data which come as CSV files and contain entries
that look like multiplication e.g "3*4". Damn Excel can't handle
these. Every chance it gets, it turns them into "=3*4" and displays
"12". 

Any one knows how to kill this behaviour? Please share the knowledge.

Henryk Birecki
0
1/16/2007 4:26:47 PM
excel 39879 articles. 2 followers. Follow

4 Replies
594 Views

Similar Articles

[PageSpeed] 11

Henryk Birecki wrote...
>I wish Microsoft stopped developing "helpful" programs. All their
>"auto" stuff leads to nothing but problems. Here is an example:

You're assuming that wasn't their intent.

>I have to process data which come as CSV files and contain entries
>that look like multiplication e.g "3*4". Damn Excel can't handle
>these. Every chance it gets, it turns them into "=3*4" and displays
>"12".
>
>Any one knows how to kill this behaviour? Please share the knowledge.

The knowledge has been shared thousands of times. Learn how to use the
Google Groups archive. You're not the first person to ask about this.

But WTH, this is akin to Excel treating entries like 1/2 as dates. I've
never seen Excel mishandle any field containing asterisks but *WITHOUT*
a leading equal sign. You need to rename .CSV files as .TXT files, then
when you open them Excel will launch its Text Import Wizard. [Excel
*SHOULD* include an option to always launch the the Text Import Wizard
when opening CSV files.] The Text Import Wizard allows you to specify
certain fields as Text, which aren't interpreted as formulas.

0
hrlngrv (1990)
1/16/2007 4:58:18 PM
Henryk

My guess is you have Tools>Options>Transition options checked.

Uncheck these, especially  "Transition Formula Entry"


Gord Dibben  MS Excel MVP

On Tue, 16 Jan 2007 16:26:47 GMT, Henryk Birecki <soaringpilot@sbcglobal.net>
wrote:

>I wish Microsoft stopped developing "helpful" programs. All their
>"auto" stuff leads to nothing but problems. Here is an example:
>
>I have to process data which come as CSV files and contain entries
>that look like multiplication e.g "3*4". Damn Excel can't handle
>these. Every chance it gets, it turns them into "=3*4" and displays
>"12". 
>
>Any one knows how to kill this behaviour? Please share the knowledge.
>
>Henryk Birecki

0
Gord
1/16/2007 5:31:37 PM
Thanks,

No, these options are unchecked together with anything else that I
could to turn off "automatic" things

Henryk

Gord Dibben <gorddibbATshawDOTca> wrote:

>Henryk
>
>My guess is you have Tools>Options>Transition options checked.
>
>Uncheck these, especially  "Transition Formula Entry"
>
>
>Gord Dibben  MS Excel MVP
>
>On Tue, 16 Jan 2007 16:26:47 GMT, Henryk Birecki <soaringpilot@sbcglobal.net>
>wrote:
>
>>I wish Microsoft stopped developing "helpful" programs. All their
>>"auto" stuff leads to nothing but problems. Here is an example:
>>
>>I have to process data which come as CSV files and contain entries
>>that look like multiplication e.g "3*4". Damn Excel can't handle
>>these. Every chance it gets, it turns them into "=3*4" and displays
>>"12". 
>>
>>Any one knows how to kill this behaviour? Please share the knowledge.
>>
>>Henryk Birecki

0
1/17/2007 1:42:13 AM
Thanks, I'll try this.  :(

Henryk Birecki

Gary''s Student <GarysStudent@discussions.microsoft.com> wrote:

>You must make Excel less "helpful".  Re-name the file to something.txt rather 
>than something.csv before opeinning it.
>
>When you don't use .csv, Excel will bring up the Import Wizard to interpret 
>the file.
>
>Now you get the chance to tell the Wizard that the field is really text and 
>not to muck with it.

0
1/17/2007 1:45:12 AM
Reply:

Similar Artilces:

Gantt Chart in Excel. Do I need conditional formatting?
Hello all. I have created a Gantt chart that shows approx. 15 tasks in each project. I am using this to show percentage of completion for each task. It is working just fine and looks great but I need it to insert a date when a task is at 100% completion to show the client the date that particular task was completed. How would I do this without ruining my chart I already have working fine? I know this is probably easy to do but I cannot seem to keep the rest of the chart intact. Is conditional formatting the answer? Thanks! You could use Rob Bovey's XY Chart Labeler to do this: h...

Sum formula is not adding up properly
I am summing up hours in Excel and the sum formula is not working Properly. For 2 of my 5 cells are adding correctly, but the other 2 when added to the formula throw the entire thing off. They are all formatted the same in 13:33 format to measure the # of hours spent on an activity. What would the reason be that two of them are not working? (it is almost like exel is substracting hours when these 2 cells are added) No real problem. Your math is probably fine; formatting needs fixing. Select the cell with the sum in it. Format this cell as Time 37:30:55 -- Gary's Student &quo...

Excel 2003 VB referencing #N/A not working but works in Excel 2007
I have a very simple macro that works in Excel 2007 but hangs up constantly in Excel 2003. Does anyone have a work around? Problem: When referencing cells that have a #N/A value, Excel 2003 craps out even if I use the ISNA application function before it. The following 2 statements will cause Excel 2003 VB to stop regardless of order if the cell that is referenced comes up with #N/A. Series = Cells(RowNumStart + X - 1, SeriesCol).Value If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then Further background.... I am actually wanting to find #...

excel formula problem #3
i want to set up a system that counts a series of dates but only say how many cells are filled, i can get this to work generally but section needs to count 3 seperate dates but only add on any one of the (eg) cell A counts the number of dates in cells B-Z but with cells D,E, i dont want all to count, only one of the three even if all are filled but it must count if any of the three are filled hope this makes sens -- Message posted from http://www.ExcelForum.com Hi try: =COUNT(B1:C1,G1:Z1)+(COUNT(D1:F1)>0) -- Regards Frank Kabel Frankfurt, Germany > i want to set up a system that c...

How do I convert .txt files into .xls in order to be able to sum.
I receive via e-mail a statement that I need to resort by date , purchase order and total by department, etc. The process I have been using is to copy and paste into Excel. However, when I try to sum the $ is will not sum. Is there some other way of converting this statement that I receive via e-mail into Excel, so that I can resort and sum? I am using Excel 2002 and my e-mail browser is Mozilla. You might be able to use File-->Open, and then let the Text Import Wizard help. Otherwise, you could use the "data isn't recognized" fix here: http://www.officearticles.co...

SP2 screws Excel. Now only puts in decimal numbers
I installed SP2 for office 2003 yesterday and now anything I type into my excel workbook shows up with .45 instead of 45 which I typed in.. Any ideas? Tools -> Options -> Edit tab. Uncheck "Fixed Decimal Places" HTH knut "Tboz" <Tboz@discussions.microsoft.com> skrev i melding news:42223421-8753-4E47-B4BE-ACED5FD1EF24@microsoft.com... >I installed SP2 for office 2003 yesterday and now anything I type into my > excel workbook shows up with .45 instead of 45 which I typed in.. Any > ideas? tools>options>calculation>precision as displ...

Transposing Excel Formula
I have the following formula to calculate the probability of an option trading below price B3 Prob = =NORMSDIST((LN(B3/B2))/((B4/100)*SQRT(B5/365))) B3 = Target Price B2 = Stock Price B4 = Implied Volatility B5 = Days to Expiration I want to transpose the formula to solve for B3, assuming a value for Prob. Here's what I got, but it doesn't give the expected answer - did I make a mistake in these unfamiliar functions?: B3= EXP(NORMSINV(Prob))*((B4/100)*SQRT(B5/365))*B2 On Feb 7, 10:14=A0am, Steveal <steve_alm...@yahoo.com> wrote: > I have the following formula to calculat...

Excel VBA
I have created a custom menu bar..a section of the code below...but d not know how to create a 2nd level to look like the example below. Period > Month > Current Year (action) Any suggestions. Thanks. ' Add a new menu Set NewMenu = NewMenuBar.Controls.Add _ (Type:=msoControlPopup) NewMenu.Caption = "&Period" ' Add a new menu item Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton) With NewItem .Caption = "&By Month" .OnAction = "Main" End Wit -- Message posted from http://www.ExcelForum.com You should find your answer here (...

How do I change the format of how my active cell in Excel is view.
I sometimes have trouble locating which cell is active in my Excel worksheet. I would like to be able to change it to something other than just a heavy black border. Here is one way that highlights the row and column of the activecell. Private Sub WorkSheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add T...

Excel Worksheet menu bars customizing for limiting users to optio.
Customizing Excel worksheet menu bar for limiting users to the options provided in that menu Hi Jim Look also at the links to MS pages on the bottom of this page http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jim" <jim@discussions.microsoft.com> wrote in message news:F8009001-5F5A-4415-9527-34C71D8DF1DB@microsoft.com... > Customizing Excel worksheet menu bar for limiting users to the options > provided in that menu ...

EXCEL 2003 #8
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match of the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding text in B1-B20 on Sheet2 to cell B2 on Sheet1. On Sheet1: =INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0)) -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike" <Mike@discussions.microsoft.com> wrote in message news:582672AF-9360-4525-9F8F-9C236D08A1A7@microsoft.com... > Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match > of > the code ("WD4") on Sheet2 i...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

Can you use the old Lotus "/ commands" in Excel 2003?
In Excel 2000 you could use a demo mode to activate the old Lotus menu commands. Can you do this in Excel 2003? Look at Tools>Option>Transition -- Kind regards, Niek Otten "DAW" <DAW@discussions.microsoft.com> wrote in message news:B5590E6E-DFE1-4A9A-8651-387B7F49D112@microsoft.com... > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do this in Excel 2003? > It's gone from xl2003. DAW wrote: > > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do thi...

create automatic response to questions in excel....
Hi, Is there a way in excel where I have 10 questions and can click on one question and get the answer to pop up in my spreadsheet? thank you, Felicia On Dec 1, 6:39 am, felicia <feli...@discussions.microsoft.com> wrote: > Hi, > Is there a way in excel where I have 10 questions and can click on one > question and get the answer to pop up in my spreadsheet? > > thank you, > > Felicia One way, with questions in A1:A10 and corresponding correct answers in A1:A10 on a hidden worksheet with tab name "Answers"... right click the tab of the sheet with the qu...

percent formula
Anyone please! How do I write a formula that gives me the total after deducting %. Example if I have $25 and I need to deduct 14% and I need to show the total which would be 21.50 -- smile Try this: =25*(1-14%) A1 = 25 B1 = 14% =A1*(1-B1) Format as General or Number -- Biff Microsoft Excel MVP "israel" <israel@discussions.microsoft.com> wrote in message news:E5C67430-5992-46A1-B9EC-40C0DC443CDA@microsoft.com... > Anyone please! > > How do I write a formula that gives me the total after deducting %. > Example if I have $25 and I need to deduct 14% and I...

What does the $ symbol mean in a formula?
What does the $ symbol mean in a formula? example: =G8*($A$14*2)*100 thanx The $ is used for absolut referencing. For e.g lets take your formula =G8*($A$14*2)*100 suppose you enter this formula in cell B2, now simply copy this formula one cell to the right (C2) by and one cell down (B3). C2: =H8*($A$14*2)*100 B3: =G9*($A$14*2)*100 So you see that The original G8 has changed, whereas $A$1 remains the same in both the new formulae because of the dollar signs. Mangesh "S" <S@discussions.microsoft.com> wrote in message news:D86597A4-C0A1-46AF-9DA6-FE06AB9C1FA0@mi...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Does anyone have a formula to calculate the car lease payments
Hi, I am just looking for a formula that will allow me to calculate the monthly repayments for a car lease. Thanks in advance The PMT function may help you out. Look at Excels help for details regarding its arguments and use. "Captain Jack" wrote: > Hi, > > I am just looking for a formula that will allow me to calculate the monthly > repayments for a car lease. Thanks in advance see if this helps http://tinyurl.com/dxnq4 -- Don Guillett SalesAid Software donaldb@281.com "Captain Jack" <Captain Jack@discussions.microsoft.com> wrote in message ...

How do I rotate a worksheet in Excel?
I know that it is possible to rotate content within a cell, but what if I want to rotate the whole worksheet? And yes, I know that I could just set it to landscape mode, but I want to modify it once the rows become columns and the columns become rows. You are limited to 256 columns (and therefore 256 rows). create a new worksheet Select your range on the original worksheet edit|copy go to A1 of the new worksheet edit|paste special|check transpose. MarkRulesTheWorld wrote: > > I know that it is possible to rotate content within a cell, but what if I > want to rotate the whole w...

Is it possible to merge workbooks in excel?
I am currently working on a project with a coworker in which we need to enter quality scores for employees into excel workbooks, with one sheet per group of employees. Is it possible, if we enter data for separate groups, to later merge this data into one master workbook? Thanks! Are you asking if you can move/copy your sheet for Group AAA into another workbook that has a sheet for Group BBB? If so, all you need to do is: 1) open both the source and destination workbooks 2) activate the source workbook 3) select any and all tabs that you want in the destination workbook (Ctrl-click on ...

Conditional formatting of text
Greetings, all - I am creating a simple spreadsheet template to be distributed to users for data entry purposes. The first column contains the names of the desired data elements. The second column contains the definition for each data element. The definitions are formatted in red text. I intend for the user to remove the definition and replace it with the proper information for each element. For example: Data Element Definition 1.0 LastName The last name of the person to whom questions about the sample should be directed. The user would replace ...

fonts are changing and text disappears
Hello when i open outlook 2003 all my maps on the left side of the screen are looking well and normal. But after a wile the font changed or a piece of the name/text disappear. Very stange i think. When i close outlook and turn it back on, everyting is back to normal but not for long. ...

TEXT STATUS -VS- Numbers
We have name of projects with 8 different status. We want to create a chart with the name of the project on the X axis and text status on the Y axis. Can this be done and if so how. Please help DDBeards If you assign numbers 1-8 for each status, you can then hide the 1-8 axis labels, and add custom ones: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DDBeards wrote: > We have name of projects with 8 different status. We want to cre...

resize all rows in the spreadsheet to fit the text?
I have an existing spreadsheet created by another user. Some of the rows are not automatically sized to display all text. Is there a command to resize all rows in the spreadsheet to fit the text? thanks email: softwaretest underscore 2003 at hotmail Church lady, try Ctrl a, to select the whole sheet, then format, row, autofit -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "the church lady" <tcl@mail.com&g...

how do I add a 'web address' to a cell in excel with a hyperlink?
how do I add a 'web address' to a cell in excel with a hyperlink? I am a new student to computers, and part of a test is to insert a hyperlink to a cell in excel linking to a web address. I have searched computer for any 'Hyperlink' connection words, but I think it not on here, Is that possible, although I have top of range computer. Anyone with a solution? I be ever so greatful. in xl2002 ctrl+k>in the link to select existing web page>either type in (at the bottom) or point to the url on your browser>enter -- Don Guillett SalesAid Software dguillett1@austi...