Format Changing to Number (from text) once exported to CSV

[Jack_881]

Hi Experts

I have a column that has data like:

20873,1679,20274,110620,110617,110618,110619,110616,110614

I have a VBA function that exports to a CSV file however this is being coverted to:

2.08732E+49

after the export in both the CSV file and also if I open with EXCEL. In the table the column is formatted as TEXT and unfortuantely the program this data is uploaded to use "," as the delimeter. Is there a way to retain the data in the text format rather than it being converted to a number after export?

Cheers

Jack

 

[rondeondo]

Hi Jack
The problem is not the export, it's how Excel interprets the CSV when you open it. I work a lot with CSV files and have found the best thing is to open in notepad then copy into excel, then you can use text to columns in Excel and specify the fields to be text or numbers. Also if you change your VBA export to use a different separator that can help as well. I use the | pipe character as it generally doesn't appear in the data I work with.

 

[Jack_881]

Thanks for the response. The sofware that imports the exported data imports via CSV. The reason for the VBA code to make the CSV is to automate the process of getting the data into the software. So in this case opening up the data to re-format sort of defeats the purpose. The software will only use a comma as a seperator so the pipe character will not work in this instance.

 

[sandy666]

it is not clear what you want to achieve with Excel
your result should be like

or like this

both are as text

 

[Jack_881]

Apologies for not being clear. The desired result as as per your first result

 

[sandy666]

I wonder why it should be this way but it's your choice, use Power Query, New Query, From File, From CSV,
in new window choose equal sign as delimiter

Edit, remove second step (Changed type)
then you can do what you want