Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Given an existing Excel 2003 document with cells of any type (integers, text, decimal, etc), how do I convert the contents of every cell to text?

And save all these changes in the same excel document?

share|improve this question
    
why not just save as a CSV file? What's the big plan? :-) –  jweede Apr 14 '10 at 13:45
    
Need to get everything to text and save as an xls –  Justin Tanner Apr 15 '10 at 7:52

4 Answers 4

up vote 3 down vote accepted

On a different sheet you can enter in cell A1:

="" & OriginalSheet!A1

Copy/Paste that for the full width and length of your original sheet. Then you can Paste Special>Values bock over the originals. That should make everything have your little green patch.

The only caveat is dates. When you apply this formula to a date field it will show the serial number of the date as text. Not the normal formatting you would have applied. To get around this, use this formula instead of the one above for date fields:

=TEXT(A1,"mm/dd/yyyy")
share|improve this answer
    
Works great just what I was looking for. –  Justin Tanner Apr 15 '10 at 7:50

You can select all the fields, right click and copy, then right click and paste special/values. You probably want to copy into a new spreadsheet so you don't lose your formulas.

Then select all the fields and do as Mark Robinson suggested: click Format Cells and chose text.

This will covert all formulas into the value they represent instead of a formula and then covert the values that are number into text.

share|improve this answer
    
Thanks, but has the same problem as marks answers the copy and pasted cells are not converted to text. –  Justin Tanner Apr 13 '10 at 21:18
    
What is it that you need to do with the text? Are all these fields numbers? –  Zooks64 Apr 13 '10 at 22:33
    
The kinda of text I need from excel is where each cell has that little green patch that indicates that its text. The cells are a mix of number, letters, anything, but I need the result to be text. –  Justin Tanner Apr 14 '10 at 1:39
    
That little green patch sounds more like a formatting item that was applied at some point. Text cells do not have that feature as a default. –  Zooks64 Apr 14 '10 at 12:04

Here is the definitive answer:

highlight the numbers and use the Data > Text to Columns command. In Page 1 of the wizard, choose the appropriate type (this will probably be Delimited). In Page 2, remove any column dividers that may have shown up to keep the data in one column. In Page 3, click Text under Column data format to indicate that this column is text.

share|improve this answer

Select all your cells Ctrl+a

Right click on the selected cells and click Format Cells

Under the Number tab select Text as the Category

Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered.

Then save your excel document

share|improve this answer
    
Thanks, but this doesn't individually format of each cell of existing data. –  Justin Tanner Apr 13 '10 at 19:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.