How do I convert all the cells of an excel sheet to text?

Question

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?

Answer 1

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")

Answer 2

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.

Answer 3

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.

Answer 4

The only way that I was able to overcome the problem of retaining only text from numbers and texts was to save my file as a .csv then close it and reopen it .. all data is then text and the file can then be saved as a .xlsx or whatever and formulae added. This enabled me to compare data from different sources and return values correctly.

Answer 5

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