# How to Add a 1 in Front of Numbers in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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:

• 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. ### 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. ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  