3

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?

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

1 Answer 1

Reset to default
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .