Have a column with numbers starting with a 0

Question

I have data that I want to import into Excel. They’re phone numbers from multiple countries. For example:

David France 0145535663
Luis Spain 62333331
Mike USA 5465555112

The numbers have different lengths, so I can’t configure a special format in Excel :( and I need to keep the "0" in front of the numbers of some country.

Do you know an easy way to do that in Excel 2010?

Answer 1

The simple way is just to precede the numbers with a single quote - '. This will turn them into strings which won't get the leading zero stripped off.

You can also format the cell to be a string rather than a number.

Answer 2

Is there anything which prevents you to format the column with the phone numbers in "Text" format?

• Right click Column, Format Cells..., Select "Text" in "Number" tab

Phone numbers are usually not used in calculations and often referred to as text strings.

Answer 3

To do this you will need to:

1. Open the MS Excel worksheet.

2. Write the number in the cell before you want to add zero.

3. Right click the mouse in the cell, select Format Cells... option. Format Cells window will be displayed.

4. Select the Numbers tab from the window then click Custom option from the left Category: section.

5. Select 0 option from the Type section.

6. Type the no. of zeroes as many as your number contains digit. For example, your number is 12345, so now you need to enter five zeros after the selected 0 in the type section i.e. 000000.

7. Click OK. The number of zeros entered by you will be converted to digits and the result would be 012345.

1