You may attach a special type of number like 1 before the local phone number of the US to make it an international identity. It is also mentionable that the country code for the US is +1. In this article, I’ll discuss seven special tricks on how to add a 1 in front of numbers in Excel.
How to Add a 1 in Front of Numbers in Excel: 7 Easy Ways
In this article, we will discuss how to add a 1 in front of numbers in Excel. Firstly, we will use the Ampersand operator. Secondly, we will go for the Format Cells option. Thirdly, we will apply the SUM function. Fourthly, we will opt for the CONCATENATE function. Then, we will resort to the NUMBERVALUE function. After that, we will use the IF function. Finally, we will use a combination of LEFT and IF functions to add 1 in front of numbers in Excel.
1. Using Ampersand Operator
The ampersand operator is used to combine or merge two or more cells in Excel. In this method, we will use this operator to add 1 in front of another number. We will use quotation marks around the 1 to add it to the number.
Steps:
- Firstly, select the C5 cell and type the following,
="1"&B5- Then, hit Enter.
- Consequently, Excel will add the 1 to the number.
- Finally, lower the cursor down to autofill the rest of the cells.
2. Utilizing Format Cells Option
Format cells are a collection of different formats where we can easily convert the appearance without modifying the number. We can use this feature to add 1 in front of another number.
Steps:
- To start with, select the cell range C5:C12.
- Then, right-click.
- From the available options select Format Cells.
- Consequently, a prompt will be opened.
- In the prompt, go to Custom under Category.
- Then, in the box under the Type option type,
1#- Finally, click OK.
- As a result, we will find a 1 added to the numbers in those cells.
3. Applying SUM Function
The SUM function is the default function for summation in Excel. We will put this function for our use. As our number consists of 10 digits, we need to add 1+0000000000 (input 10 times zero). And sum up this number with local phone number.
Steps:
- To begin with, choose the D5 cell and enter the following,
=SUM(B5+C5)- Hit Enter.
- As a result, we will see the number 1 is added to our existing number.
- Lower the cursor to autofill the rest of the cells.
4. Using CONCATENATE Function
The CONCATENATE function adds two or more texts together. In this method, we will use this function to add a 1 in front of another number. The 1 will be the first argument of the function and the number will be the second.
Steps:
- Firstly, select the C5 cell and enter the following,
=CONCATENATE(1,B5)- Press Enter.
- As a result, Excel will add a 1 in front of the number.
- Move the cursor down to the last cell to autofill the cells.
5. Applying NUMBERVALUE Function
The NUMBERVALUE function turns value into a number with an individual separator. We may easily employ this function in our data set. This will add the 1 and the number.
Steps:
- Firstly, choose the C5 cell and enter,
=NUMBERVALUE(1&B5)- Then, hit the Enter button.
- As a result, we will see a 1 before the number.
- Finally, lower the cursor down to autofill.
6. Utilizing IF Function
The IF function is used to set conditions in Excel. In this method, we will use a condition to combine the 1 with the number.
Steps:
- Firstly, choose the C5 cell and enter the following formula,
=IF(B5<>"","1"&B5,"")- Then, press the Enter button.
- As a result, 1 will be added in front of the number.
- Lower the cursor down to the last cell.
Read More: How to Concatenate Cells with If Condition in Excel
7. Using Combination of LEFT and IF Function
Imagine, the number starts with zero or has leading zeros. Now, if you want to apply the earlier tricks, you won’t get the expected result. Because a number with a leading zero is only available completely in Excel when it is stored as text. In this trick, we’ll use the combination of IF and LEFT functions for our dataset.
Steps:
- Firstly, click on the C5 cell and type,
=IF(B5="","",(0+(LEFT(B5)="0")&B5)+0)- After that, press Enter.
- As a result, we will get the desired result.
- Finally, lower the cursor down to the last cell to autofill the cells.
Download Practice Workbook
You can download the practice book here.
Conclusion
In this article, we have discussed 7 handy ways how to add a 1 in front of numbers in Excel. This will help users to modify the numbers that are typed incorrectly and make amends by adding a 1 before them.
Related Articles
- How to Combine Text and Number in Excel
- How to Concatenate Numbers in Excel
- How to Concatenate Numbers with Leading Zeros in Excel
- How to Add Leading Zeros in Excel by CONCATENATE Operation
- How to Concatenate and Keep Number Format in Excel
- How to Concatenate Date That Doesn’t Become Number in Excel
- How to Combine Date and Text in Excel
- How to Concatenate and Keep Currency Format in Excel
- How to Combine Text and Numbers in Excel and Keep Formatting
