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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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,123,198
Messages
5,600,280
Members
414,374
Latest member
akbir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top