Treat numbers as text

I have an Excel spreadsheet with a range of values which are numbers that go to up 20 decimal places, unpivoted from another sheet using the trick from here.

The trouble is the cells are only displaying 10 digits so, for example, even though the value is 5.46827166811115 it is showing as 5.468271668.

I've tried setting the format to text but it still wants to treat it as a number, the number of decimal places varies so I can't use a fixed #.### format. The only way I can get it to show is to format the cells as text and to just select and then click in the entry box for each and every cell!

It then shows a warning that the number in the cell is formatted as text or preceded by an apostrophe but at least it's showing the full value.

I did find a VBA script that just did something stupidly simple like cell.Value = cell.Value for the selection which seemed to work but I can't find it anymore and I can't reproduce that now.

Surely there's an easier way to do this? It wouldn't matter so much but when I import this data through SSIS into a VARCHAR(MAX) it's getting the truncated values!

so - do u want to see the entire number? 5.46827166811115 but do not want to see the warning? can you upload your workbook?

The warning doesn't matter.

I've actually found a solution which it to create a new column B and set the formula to =""&(Ax) which forces it to treat the number as text. I then have to copy this, paste as values and delete the original column A.