Leading 0 of the phone number in Excel is deleted

Source: Internet
Author: User

I created a list of contacts using Excel. But I found a problem with the display of a phone number leading zero. When you enter "090", the result becomes "90". Why?

Put this picture away. expand this picture



If no leading zeros are displayed, the result is not a correct phone number.           How can I show zero? First, I'll tell you why you didn't show a leading zero. Excel can handle two types of numbers: The numbers used for calculations and the numbers that are treated as text.

Well.           Where is the difference? In calculations, 090 and 90 are treated as equals. Therefore, Excel cannot differentiate between "090-080" and "90-80".

They are all numbers, but they can't be added or deleted for phone numbers, right? That's why we have to specify whether the number is for calculation or text.

Now I get it.           Because Excel treats these numbers as numbers for calculation, no leading zeros are displayed. That's true. All you have to do is remind Excel that these numbers should actually be treated as text.

Tell me how to operate. There are two main methods. First, let me tell you a simple method. You can hyphenate a phone number, or you can insert an apostrophe (') at the beginning of the phone number. That way, Excel will recognize these numbers as text.

Put this picture away. expand this picture

Put this picture away. expand this picture

I know. As has been said, you may not want to hyphenate the phone number. Adding apostrophes to all the phone numbers is a tedious process. Then another method is very convenient.

Select the cell that contains the phone number, and then click the right mouse button. From the menu that appears, click Format Cells .

Put this picture away. expand this picture



After the format Cells window is displayed, click the Number tab and select text in categorize .

Put this picture away. expand this picture

Complete.

Put this picture away. expand this picture

The phone number entered before this setting takes effect is processed as a numeric number, and its leading zeros are removed. Therefore, you will then need to add a leading zero. However, after you complete this setting, the number you enter will have a leading zero. I recommend that you format the cells first before you start typing the phone number.

What is a small green triangle in the upper-left corner of the cell?

Put this picture away. expand this picture

Click the cell.

You can see this display of

Put this picture away. expand this picture

Error icon? If you click the icon, the " number stored as text " error is displayed, but don't worry. You can ignore this error without any problems.

Put this picture away. expand this picture



However, if you do not want to display this error, click Error Checking Options , and deselect the number in text format or the number preceded by an apostrophe option. Finally, click OK .

Put this picture away. expand this picture

Oh! The green triangle is gone!

Put this picture away. expand this picture



Oh! The green triangle is gone!

Put this picture away. expand this picture

Very good. Excel aligns the values to the right and aligns the text to the left. You can differentiate between numbers and text by checking their alignment

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.