override auto formatting

[jwils]

I am converting word files of exam questions to excel . Many of the answers are dollar amounts, percentages, or dates. Though I specify to convert as text these cells are auto formatted as currency, percentage, or date. The final file is then used to create a program database which doesn't recognize these formats. I currently am manually changing each cell to text format and reinserting the $ or % symbols. Is there a way to turn off the auto formatting of text or trick it into not seeing the content as currency/dates/percentages. Thanks

[fredblogs]

Hi Jwils, Have you tried selecting the whole sheet and then select the format dialog and set it as text on the number tab? I might have misunderstood but it seems to work for me.

[jwils]

Yes, I do that, however, it removes the $ or % symbol and the commas which I then have to manually replace in each cell. Example: $371.20 when set to text format becomes 371.2 I then have to manually retype the $ sign and add the 0 at the end. That is why I was hoping there was a way to turn off the auto formatting feature.

[fredblogs]

I can't replicate the problem. What version of excel are you using? I'm in 2007.

[jwils]

Appears I have an older version. Excel 2003 on my PC. However I have the same occurrence on my Mac which is running 2008. The file is a word text file opened in excel using a character as the deliminator.

[ChemistB]

Okay, I'm confused. You're importing the data as text and then added $, extra decimals as needed? It would help to see your sheet layout to come up with a plan. When you export them to another program, are you trying to use them numerically? Once you import as text and then add extra symbols, they will be seen as text/strings.

[jwils]

I import a delimited text file that has the information correctly shown, such as $371.20 for answer A. The data shows up in the excel cells the same however instead of being formatted as straight text it has been changed to the currency format for that cell. My programmers database conversion doesn't recognize currency, percentage or date formats. thus I have to make sure all cells are either text or general. When I change the cell with the currency value in it to the text format, excel changes it from $371.20 to 371.2. I then edit the cell and type in the $ sign and the 0 so that it will show as $371.20 in the final database as text. It is a very simple spreadsheet. Columns are: Question, Answer A, Answer B, Answer C, Answer D, Explanation, Correct Answer. Since many of the questions are math problems involving payments or monetary value, the answer choice columns will have dollar values. That is where my problems with the autoformatting begins.

[jwils]

Thanks all. I think I have resolved my problem. It appears when doing the import I have to mark each column to import as text. I was clicking on the text option thinking it addressing all columns not just the first and that I had to highlight each cloumn it recognized and make the change.