VBA: Format all data in a column as text (aka: Undo auto-Scientific Notation)

[mrkevelev]

I can convert a column of data to type: text. But it still shows large numbers as scientific notation. For example: in a cell it reads: "6.40001E+14" but in the formula bar it reads: "640001171111044". I need it to say the latter, as these are Serial Numbers, and no math is being done. What I can do after running the code is double click on the cell and it "fixes" itself and appears the way i want, but I need it to work automatically.

The code I'm using to convert to text is:

Sub Serial_Number_Cleanup() 'Make sure that column F is chosen (For SN's)
Columns("F:F").NumberFormat = "@"
End Sub

Why doesn't excel express it the way it says it will?

 

[alansidman]

Using Power Query, Import the data. Highlight the column with the data. Right click and select "Change Type" and select "Text." Close and Load.