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

Jack_881

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,379
it is not clear what you want to achieve with Excel
your result should be like
csv1.png

or like this
csv2.png


both are as text
 

Jack_881

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Apologies for not being clear. The desired result as as per your first result

1600587425354.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,379
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
es.png

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

Watch MrExcel Video

Forum statistics

Threads
1,118,293
Messages
5,571,365
Members
412,385
Latest member
OChambo94
Top