The annoying excel scientific notation

[cchai]

I have a column which is the supplier item code, it contains text or a long barcode (so it is not pure long "number" barcode), 

e.g.

ABC123456

1000000000000000

The data are from some source that I cannot control, I already changed the format of the column to text, but it still showing the scientific notation (e.g. 1E+15), so how can I completely stop this annoying scientific notation in text format?

And I think it is a bug! I already specify it is a "text" (a TEXT!), not a number, how come excel will still showing the scientific notation (but when I double click into the cell, it shows the original value in digits, so the value is here not missing, **bleep** it!)

[#2]

@cchai 

#1 The data are from some source that I cannot control What kind of Source is it (Csv/text file,...)

#2 What version of Excel do you run?

#3 On which platform/OS (Windows, Mac...)?

Depending on the above you might have a solution with Get & Transform aka Power Query:

In attached file query ExternalSource simulates an external data source, query ImportedData just transform the data types as desired (Text for Barcode)

+ Feel free to UpVote Give user control over conversion from numbers to other formats

[#3]

In the formula bar, do they show as original number? for example: 1000000000000000 instead of 1E+15.

[#4]

@cchai 

Assuming the formula shows original number, maybe this way:
Use macro to add apostrophe (') in front of the numbers.
Something like this:

Sub to_Text()
Dim c As Range
 For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    c = "'" & c.Formula
 Next
End Sub

Before

After