Keep leading zeros when converting to CSV!

Question

Hi all, and thank you for your time!

I am looking for a way to convert a selection from excel to CSV Comma delimited without losing leading zeros.

The column in question is currently pulling info from a different tab and converting it to a text format using the "TEXT" formula, and adding leading zeros. If I copy-paste info (values) to another workbook and save as CSV, I lose leading zeros!

Can you help?

This is the formula...

=IF(TEXT(INDEX(Sheet2!$A:$V,$L3,10),"0000")="0000","",TEXT(INDEX(Sheet2!$A:$V,$L3,10),"0000"))

And this is the result...

Job ID
0204
0012
0205
0205
0204
0202
0202
0202
0202

Answer 1

CSV will convert text to number when you open it. But the leading zero are still there if you open them in notepad.

 Applying Power Query to Keep the Leading Zeros

• Firstly, go to the Datatab from the Ribbon.
• After that, select From Text/CSVoption from Get & Transform Datagroup.
• Now, navigate to yourCSVfile and select it.
• Following that, click on Import.
• Firstly, click on Transform Data.

Afterward, a Power Query Editorwindow will be open on your worksheet as shown in the image given below.

• Now, click on the marked area in the Column1.
• After that, select the Textoption from the drop-down.
• Subsequently, a dialogue box will open and choose Replace Current.
• At this stage, you will be able to see that zeros in that****column are back as shown in the following image.

Answer 2

Hi,

I believe your formula

=IF(TEXT(INDEX(Sheet2!$A:$V,$L3,10),"0000")="0000","",TEXT(INDEX(Sheet2!$A:$V,$L3,10),"0000"))

is not adding leading 0's but formatting to show leading 0's.

An alternative formula approach would be:

=RIGHT("0000"&Sheet2!A1,4)

A1 is just my quick address for a cell containing something like 204. You need to adjust for your references. This basic formula returns text numbers with leading 0's not formatted numbers. You can save it as a CSV file without losing the leading 0's.