stop a text field reverting to scientific notation

[grapevine]

I have a CSV file that is reworked with a macro so that the information can be read by a printing machine.

One of the columns contains a series of 12 digit barcodes that needs to be read as text.

Currently the number appears in scientific notation.
Problem -
If I convert directly to text, it still appears as scientific notation
I can convert it to a number so that all 12 digits appear but when I try and change the column to text it reverts to scientific notation.
I have tried inserting another column, making it text then copying and pasting the data in, but the new column reverts to number.

If I put a ' in front of the number it does stay as the proper number but this is not practicable as the file could easily be several hundred rows long and many files are processed on a daily basis. Is there any VBA which can convert the number to text but still retain the full 12 characters?
I am using Excel 2007, although the file is saved in 2003 compatibility mode.

Any help gratefully received.

 

[Andrew Poulsom]

Try changing the file extension from csv to txt. Then the Text Import Wizard should fire and you can choose the format of the column at Step 3.

 

[grapevine]

Many thanks for your suggestion, but unfortunately the file is already split by others who may or may not remember to make the column text.(we cannot control that end) The file is then stored on a system until it is ready to be used when it manipulated by the macro and the results fed into the print machine. There are several people who could run the macro.

I want the macro to ensure that the field is always correct regardless of how it is split. If a scientific notation slips in unnoticed the entire printing batch has to be redone causing unnecessary time and cost.

I need some code to incorporate into the macro to check this column and ensure it is showing all 12 characters and is a text field.

Many thanks