Cells in CSV file not retaining formatting

WLHagen

Board Regular
Joined
Sep 18, 2009
Messages
177
I have some data in CSV files that I need to import in to another program. Column A in the file contains the location of where the data in subsequent columns is mapped in the software. The locations in this software contain both numbers and letters, so I've formatted column A as TEXT. However, when I save the file, close it, and reopen the file, the formatting reverts to what Excel thinks I want. The cells in column A that I have difficulty with are cells that contain a leading zero or an E in it. For example:

084 - reverts to 84
085 - reverts to 85
065E1 - reverts to 65E1

TIA for any help!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
If you open the file in a text editor, such as notepad, do the leading zeros exist?
 

WLHagen

Board Regular
Joined
Sep 18, 2009
Messages
177
If you open the file in a text editor, such as notepad, do the leading zeros exist?

When I opened the file in Notepad, the 065E1 was correct, but 084 and 085 came in as 84 and 85
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
In that case the original file did not have leading zeros, perhaps the cells had a custom format, before being saved as a csv
 

WLHagen

Board Regular
Joined
Sep 18, 2009
Messages
177

ADVERTISEMENT

Thanks. Since I created the file in Excel and didn't pay attention to formatting until after I had these errors in the csv version of the file, what is the solution?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You will need to ensure that the cells are text & have a leading zero, rather than simply formatted to show a leading zero. That way when you save to csv the leading zeros should be retained.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Simply formatting the cells should be sufficient. Excel creates CSV files using the displayed values.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Never knew that Rory, thanks. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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