Saving to CSV - retain leading zero in tel number?

[1cyril1]

I have a column of telephone numbers in Excel all starting with zero (0). When I save as CSV and re-open the zeros have disappeared! I've tried formatting in Excel as 'Text' and as 'General' before saving to CSV but the result is the same. Unfortunately, for the task I'm doing, I'm forced to use CSV and I must have the zero present at the start of each number.

Anyone got a solution please?

Cyril

 

[Richard Schollar]

Hi Cyril

You probably do have the leading 0s within the csv file, but Excel 'helpfully' interprets the csv file on opening and truncates leading zeroes from columns it interprets as holding numerical data. You could avoid this by changing the file extension in Windows Explorer to .txt and then opening in Excel (which will activate the text import wizard where you can specify this column as Text so it doesn't truncate).

Hope this helps!

Richard

 

[Andrew Poulsom]

As an experiment I opened a new worksheet, formatted A1 as Text and entered 01234. I entered 1234 in A2 and formatted it as "00000". I saved the workbook as a csv file. When opened in Notepad I got:

01234
01234

being a faithful representation of how the data appeared in Excel.

But Excel is too clever by half when opening csv files and it converts both entries to a number. One solution is to use the a txt file. Then the Text Import Wizard allows you to choose the format of each column at Step 3.

 

[1cyril1]

Yep, the leading zero is present when opened in good old Notepad and that will solve my immediate problem (note: renaming .csv file to .txt didn't work out)

Many thanks!

 

[Richard Schollar]

Hi Cyril

I'm gald you have a workaround, however, I am intrigued why renaming it to .txt didn't work - are you getting the text import wizard activated when you click Open within Excel and navigate to the file? Note that you can't click on the file to open it up in Excel thru Windows Explorer as then it will be opened in Notepad - you have to do it thru File>Open in Excel.

You would need a delimited file type (comma delimiter) and you must specify the relevant columns containing the leading zeroes to be Text format. It certainly works for me on my machine.

Anyway, good luck!

Richard

 

[1cyril1]

Richard,

I've tried again and your solution works fine if the spreadsheet is saved from Excel as .txt or .csv and opened subsequently in Notepad. Previously, I had tried renaming a .csv file to .txt in Explorer and this didn't work out - the leading zeroes weren't visible in either file (I can only conclude that I overlooked the format as text step before saving as csv!)

Thanks again!