Need to keep leading zero's when saving to .CSV in Microsoft 365 MSO

Question

I am using Microsoft Excel for Microsoft 365 MSO and have a list of part numbers that are numbers stored as text with leading zeros.  When I save as .CSV for file import, the leading zeros disappear.  I need the .CSV file to retain the leading zeros.  I have tried reformatting as text with no success.  I have tried using the Convert Text to Columns Wizard with no success. I tried formula =LEFT(A2,LEN(A2)) and it worked until I need to copy and paste values, then it pastes it as Number stored as Text. Please help me figure this out.

Answer 1

Lots of options for retaining or recreating leading zeros.

The "modern" way to import data is using PowerQuery

Handling Leading Zeros and a Little M-Code**** 2016 12 16       Oz du Soleil
Here’s another video with warning about some things that native Excel handles easier than Get & Transform (Power Query) does, but if we have to do this in Get & Transform, here’s how.
Challenge: handling leading zeros.
Excel is known for clipping off leading zeroes. If you need a the month of March to be: 03   Excel will turn it into: 3
Or, account numbers that are always 6 digits,
Excel will turn 002251 into 2251
This video shows how to force native Excel to recognize leading zeros. Also, the M-Code use of
   Text.PadStart()
to force Get & Transform to honor leading zeros.
You also see the insertion of steps in a Get and Transform Query, problems with text versus number formatting. Oh!
.

Get Excel to Handle Social Security... **** 2014 03 21
https://www.accountingweb.com/technology/excel/how-to-get-excel-to-handle-social-security-numbers-properly
A senior financial analyst named Lisa asked a question related to Social Security numbers. She periodically receives employee lists where the Social Security numbers aren’t necessarily in a uniform format. For instance, some are all numeric, others have dashes, and some might be text-based. Any data analysis involving look-up functions in Excel requires that our data be clean. Here are some techniques for cleaning up the numbers.
.  *  eliminate extraneous characters, such as dashes
.  *  convert text-based numbers to values
.  *  add the dashes and leading zeroes back
.

********************************

The "mid" school way to handle leading zeros is using formulas

Top 5 Ways to **Add Leading Zeros** (Formulas) / Adding Leading Zeroes to ZIP Codes          /
https://excelchamps.com/blog/leading-zeros/
There are few default rules in Excel which can annoy you. One of those rules is you can’t add a zero before a number. The  reason behind this is:
A zero before a number makes no sense and no value to the number itself.
Whenever you try to insert a zero before a number, Excel removes it ... <sigh>
.    Convert a Number into a Text
.    Use Concatenate Function
.    Using REPT Function
.    Create a Formula with TEXT Function
.    Apply Custom Format ?
..

Change or convert number to text in Excel?
https://www.extendoffice.com/documents/excel/671-excel-change-number-to-text.html
Sometimes you may not want the numbers in the cells getting involved in calculating, or you may want to display leading zeros in numbers in cells. For doing so, you may need to change a number into text. The following methods can convert numbers in cells to text in Excel.
.  *  Convert number to text with Text function
.  *  Convert number to text with Format Cells command
.

Why You Should Be Using Custom Number Formatting In Excel
https://www.xelplus.com/why-you-should-be-using-custom-number-formatting/
Let’s see a few Excel Custom Number Formatting tips that will not only save you time, but also help you avoid common spreadsheet mistakes.
First, we’ll see the formatting in action and how it helps tell our story in the most effective way.  After that, we’ll look at the underlying rules and understand exactly how all this is produced.
The rule is quite simple.  Once you remember the rule, you’re set.
.  *  Scenario #1 – Thousands Separator with Zero-Decimal Precision
.  *  Scenario #2 – Hide the Zeros
.  *  The Custom Number Formatting Rule: Pos; Neg; Zero; Text
.  *  The Interchanging of Format Ideas
.  *  Reusing Custom Number Formats
.  *  Preserving Leading Zeroes
.  *  Adding Text to Numbers
.  *  A Common Number/Text Mistake
.  *  The Secret to Hiding Data
.  *  Resetting the Number Formatting to the Excel Default
ET MR Custom & Conditional Formats.docx.

********************************

These articles tell you how to use the "old school" using the "Text Import Wizard"

! Convert CSV to Excel: how to import CSV files into Excel spreadsheets ****                   2014 05 01    Svetlana Cheusheva
https://www.ablebits.com/office-addins-blog/2014/05/01/convert-csv-excel/
In this article you will find 3 quick ways to convert CSV to Excel. You will also learn how to import multiple CSV files in a single workbook and how to handle the cases when some data from a CSV file isn’t correctly displayed in an Excel sheet.
.  *  How to convert CSV to Excel
.  *  Converting CSV to Excel: problems and solutions
.  *  CSV file is incorrectly displayed in Excel (all data is displayed in the 1st column)
.  *  Leading zeros are lost when you open a CSV file in Excel
.  *  Values are changed to dates when converting CSV to Excel
.  *  Importing multiple CSV files in a single Excel workbook
.

Handling Leading Zeros in CSV Files 2020 05 09
https://excelribbon.tips.net/T010262_Handling_Leading_Zeros_in_CSV_Files
When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel reflects what is really in the file. If you import a file and find that Excel strips off leading zeros from  what it imports, there are a number of possible reasons. This tip explains how you can track down the problem and correct it.
.

Answer 2

To:  Becky
re:  "keep leading zero's"

One more way...

My free Professional Compare workbook has a option to do that...

At the bottom left of the image, is the option that becomes available

after clicking the "Clean Data" button.

Download from OneDrive...

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-\_-zLi49O

'---
NLtL

Answer 3

Hey NLtL:

In your screen cap I see options to remove spaces, I don't see an option to add spaces.

Do you have an online help page for it? Or just in the download?

PS: the link is dead

Answer 4

To:  Rohn007

re:  Add spaces?
No ability to do that. 
Where/when would that be necessary or desired?

re:  Instructions available?

I've tried to make the program as simple as possible.
The three numbered steps (shown in the image) are all you get.
Once a button is clicked you are transferred to the applicable sheet and are told to make a selection (input box).  The 'Clean' operation is the sole exception; you make your choice from the MsgBox and the sheet gets cleaned.

re: dead link
Thanks for letting me know.
Can you paste the dead link into the browser and get to my OneDrive?

I tested it by logging out of the forum and OneDrive and it did work.
Do you have any suggestion about fixing it for everybody?

'---

PS: The Custom_Functions add-in has a separate 5 page Word.doc help file and a 'cheat sheet' is offered when the add-in loads.

NLtL

Answer 5

The original question is "Need to keep leading zero's when saving to .CSV".

So, OK it is not so much add zero's as prevent excel from removing them

.

I'm in Edge. I copied the link into Edge. It still didn't work

Here is the error.

<snip>

This item might not exist or is no longer available

This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information. 

</snip>