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.

I received an Excel file (.xlsx) that has a column like this:

1.6000
1.6050
1.7000
5.0

And so on. The column appears to be a number type. I need to convert this column to text. When I try to Format Cell and change to Text, it changes the data like so:

1.6
1.605
1.7
5

This is incorrect and does not match the actual data. How can I change this column to text without losing data?

share|improve this question
    
Is there a fixed number of leading/trailing zeros you'd like to keep? –  amiregelz Aug 22 '12 at 19:47
    
@amiregelz All of them that appear in the file. These represent job codes and not mathematical numbers. –  JBurace Aug 22 '12 at 19:49
    
Is there a standard number of digits after the decimal, or does it vary? –  Excellll Aug 22 '12 at 20:06
    
@Excellll It's typically 4 digits, but I doubt it confines to that standard 100%. –  JBurace Aug 22 '12 at 20:12
    
50 in your example is supposed to be 5.0, right? –  Excellll Aug 22 '12 at 20:38

1 Answer 1

up vote 0 down vote accepted

If you look at Format Cells, do these cells show up as Numbers with 4 Decimal places?  If so, then, in a scratch column, set the first cell to

=IF(CELL("FORMAT",A1)="F4", TEXT(A1,"0.0000"), "THIS ONE IS DIFFERENT")

Drag to fill the column.  Find the ones that aren’t four digits and either fix them by hand individually or make the formula smarter.  Then copy and paste values.

share|improve this answer

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.