How to add zeros to phone numbers in Excel
By default, when you enter a phone number or a sequence of numbers with a leading zero, all zeros at the beginning of that number will be lost. So if you want to enter the phone number, it will not display the leading zeros. So how can I add zeros to numbers in Excel? Invite you to follow the article below.
Here are 5 ways to add zeros to phone numbers in Excel, you can choose the appropriate way to do it.
Method 1: Convert cell format to text
First, you type the apostrophe 'into the box and then enter the phone number you want.
So your phone number will be displayed with both leading zeros.
Or if you need to enter multiple phone numbers, you format all cells to enter the phone number by:
1. Select the cells to format text, then right-click and select Format Cells ( Ctrl + 1 key combination ).
2. In the Format Cells dialog box , in the Number tab, select Text in Category and click OK .
3. Enter the phone number in the cells you have formatted.
Doing this way you will see a green tick on the top left of the box, when selecting cells will appear yellow warning signs. To turn off the warning, select the box and select 1 or select all the cells, next select the yellow warning icon and select Ignore Error.
So you will be as follows:
Method 2: Set the format '0' #
Step 1 : Select all the cells you want to enter the phone number and right-click and select Format Cells (Ctrl +1).
Step 2 : In the Format Cells dialog box, select Custom and enter '0' # in the Type box and then click OK .
So you can enter the phone number containing the first 0 without losing.
Method 3: Set up Custom and separate phone numbers arbitrarily
To add zeros and separate phone numbers, do the following:
Step 1: Select the cells to enter the phone number and right-click and select Format Cells.
Step 2 : In the Format Cells dialog box , in the Number tab, select Custom and enter the setting in Type is 0 ### ### #### or you can customize it as you like (for example 0 # #### ##### or 0 ## ## ## #### or 0 ### ## ## ###) and press OK .
So when you enter the phone number, there will be zeros and number separators according to the option you have set.
Method 4: Add the - or signs. or spaces between phone numbers
In the process of entering phone numbers, you can use hyphens, dots or spaces between numbers to convert numeric data into a string so you will enter the number before the phone number.
Method 5: Use the CONCATENATE function
If you have already entered a list of phone numbers but lost the leading zero, you can use the CONCATENATE function to add 0 to the beginning of the phone number instead of having to re-type the whole phone number. . You add 0 to your phone number as follows:
Step 1: Create a full phone column, in the first cell in this new phone column you enter the function = CONCATENATE ("0"; B6)
So you will add 0 before the phone number:
Step 2 : Copy the formula to the bottom cells by dragging the mouse to the bottom right of the cell containing the function with a + sign .
Hold down and drag down the rest of the cells to copy the formula.
So you have added the number 0 to all phone numbers
Step 3: After adding, copy the full phone column.
Step 4 : Paste the full phone number into the old phone number column, you choose to paste data as shown below to not paste the whole formula into the old phone number column.
You will get the following:
Step 5 : Next you need to delete the phone number column containing the function formula.
To hide the blue warning in the old telephone column, select all phone numbers and click the yellow icon -> Ignore Error.
So you have added the number 0 to the phone number.
The above are ways to add zeros to phone numbers in Excel, so you can enter the phone number into Excel without losing zero. I wish you success!
You should read it
- How to Remove Leading or Trailing Zeros in Excel
- 3 Quick ways to fix the problem of Excel losing leading zeros
- How to Prevent Excel from Removing Leading & Trailing Zeros
- The leading zero format in Excel number ranges is easy
- Instructions to stamp negative numbers in Excel
- Guide to look up phone numbers with Google and Facebook
- PI (PI Function) in Excel - How to use PI numbers in Excel
- Split numbers from strings in Excel
May be interested
- How to extract numbers or text from Excelmicrosoft excel is great for working with both numbers and text, but if you use both in the same cell, you may have some difficulties. fortunately, you can extract numbers or text from cells to work with your data more efficiently.
- Instructions to change phone numbers associated with home bankingcurrently, some banks have sent notices and support methods for you to update your phone number at home such as message confirmation, conversion via website system, applications, ...
- How to format numbers in Excelhow to format numbers in excel in the process of working, processing data in excel sometimes you need to format numbers in excel so that data can display the number format in accordance with the requirements to be processed. you do not know how to format numbers in ex
- Some experience in Excel spreadsheetsin excel there are many good tips that only those who have ever discovered, please introduce to you some of the experience in excel.
- How to convert negative numbers to positive numbers Excelto convert negative numbers to positive numbers and vice versa on excel, you only need to perform a very simple operation.
- How to convert Apple Numbers documents to Microsoft Excelnumbers is spreadsheet software specifically for apple devices both on phones and on computers. basically, apple numbers works similar to microsoft excel, so you can completely convert documents between these two toolkits to serve each specific working situation.
- Instructions for converting numbers to words in Excelto convert numbers to words in excel, you will use a support tool, instead of typing by hand, which is not accurate and takes a lot of time to do. below are some ways to convert numbers to words in excel.
- How to transfer subscribers 11 numbers to 10 numbers in Android phone bookif manually converting all old phone numbers to new ones, it will take a lot of time and effort. moreover, the time to transfer 11 numbers to 10 numbers is not far away, expected september 15th. so, network administrator will guide you how to transfer 11-digit subscriber to 10 numbers in android phone book in a simple and fast way.
- How to convert numbers into words in Excel?in excel, entering and typing amounts is very tiring, sometimes confusing. so why do this work faster but still have to ensure accuracy? tipsmake.com will introduce you to a support tool, or more accurately, an additional add-in for microsoft excel, very convenient for accounting, finance, people who have to work with big amounts ...
- How to round whole numbers in Excelhow to round whole numbers in excel in excel there are many mathematical functions used to round numbers. today software tips will guide you some functions that can be used to round whole numbers in excel.