Saving to CSV - retain leading zero in tel number?

1cyril1

Board Regular
Joined
Mar 31, 2003
Messages
89
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Mar 31, 2003
Messages
89
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!

:biggrin:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Mar 31, 2003
Messages
89
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!
 

Forum statistics

Threads
1,136,427
Messages
5,675,777
Members
419,585
Latest member
popsin

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