Md. Abdul Kader
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.
Download Practice Workbook
You can download the practice book here.
7 Easy Ways to Add a 1 in Front of Numbers in Excel
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 the LEFT and the IF functions to add 1 in front of numbers in Excel.
1. Using Ampersand Operator
The ampersand operator is used to combine two or more texts 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.
Read More: How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
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.
Read More: Concatenate Numbers in Excel (4 Quick Formulas)
Similar Readings
- How to Concatenate Number and Text in Excel (6 Methods)
- Bold Text in Concatenate Formula in Excel (2 Methods)
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
- How to Concatenate Columns in Excel (8 Simple Methods)
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.
Read More: How to Concatenate String and Integer with VBA (5 Ways)
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.
Read More: How to Concatenate in Excel (3 Suitable Ways)
5. Applying NUMBERVALUE Function
The NUMBERVALUE function turn 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.
Read More: How to Concatenate and Keep Number Format in Excel
Similar Readings
- How to Concatenate Multiple Cells in Excel (7 Easy Ways)
- Combine Rows into One Cell in Excel
- How to Merge Rows in Excel (2 Easy Methods)
- Combine Multiple Columns into One Column in Excel
- How to Concatenate Two Columns in Excel (2 Suitable Methods)
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 (5 Examples)
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 stores 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.
Read More: How to Add Leading Zeros in Excel by CONCATENATE Operation
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 Concatenate with Delimiter in Excel (5 Easy Ways)
- Concatenate Decimal Places in Excel (5 Examples)
- How to Copy CONCATENATE Formula in Excel (5 Easy Ways)
- Combine CONCATENATE & TRANSPOSE Functions in Excel
- How to Concatenate Formula and Text in Excel (4 Examples)
- CONCATENATE vs CONCAT in Excel (2 Ideal Examples)
- How to Concatenate Arrays in Excel (With 2 Conditions)
