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.
How to add zeros to phone numbers in Excel Picture 1
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.
How to add zeros to phone numbers in Excel Picture 2
So your phone number will be displayed with both leading zeros.
How to add zeros to phone numbers in Excel Picture 3
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 ).
How to add zeros to phone numbers in Excel Picture 4
2. In the Format Cells dialog box , in the Number tab, select Text in Category and click OK .
How to add zeros to phone numbers in Excel Picture 5
3. Enter the phone number in the cells you have formatted.
How to add zeros to phone numbers in Excel Picture 6
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.
How to add zeros to phone numbers in Excel Picture 7
So you will be as follows:
How to add zeros to phone numbers in Excel Picture 8
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).
How to add zeros to phone numbers in Excel Picture 9
Step 2 : In the Format Cells dialog box, select Custom and enter '0' # in the Type box and then click OK .
How to add zeros to phone numbers in Excel Picture 10
So you can enter the phone number containing the first 0 without losing.
How to add zeros to phone numbers in Excel Picture 11
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.
How to add zeros to phone numbers in Excel Picture 12
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 .
How to add zeros to phone numbers in Excel Picture 13
So when you enter the phone number, there will be zeros and number separators according to the option you have set.
How to add zeros to phone numbers in Excel Picture 14
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.
How to add zeros to phone numbers in Excel Picture 15
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)
How to add zeros to phone numbers in Excel Picture 16
So you will add 0 before the phone number:
How to add zeros to phone numbers in Excel Picture 17
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 .
How to add zeros to phone numbers in Excel Picture 18
Hold down and drag down the rest of the cells to copy the formula.
How to add zeros to phone numbers in Excel Picture 19
So you have added the number 0 to all phone numbers
How to add zeros to phone numbers in Excel Picture 20
Step 3: After adding, copy the full phone column.
How to add zeros to phone numbers in Excel Picture 21
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.
How to add zeros to phone numbers in Excel Picture 22
You will get the following:
How to add zeros to phone numbers in Excel Picture 23
Step 5 : Next you need to delete the phone number column containing the function formula.
How to add zeros to phone numbers in Excel Picture 24
To hide the blue warning in the old telephone column, select all phone numbers and click the yellow icon -> Ignore Error.
How to add zeros to phone numbers in Excel Picture 25
So you have added the number 0 to the phone number.
How to add zeros to phone numbers in Excel Picture 26
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 add leading zeros to numbers in Excel
- How to write leading zeros in Excel
- 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
- How to extract numbers or text from Excel
- Instructions to change phone numbers associated with home banking
May be interested
How to write exponential numbers in Excel
Conditional counting function in Excel
How to sort dates in ascending and descending ways in Excel
How to convert Vietnamese accented into unsigned very fast like Unikey
Typing, abbreviation in Word, Excel with AutoCorrect
How to use Highlight in Word - Create and delete Highlight in Word?