Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

typing formula to show how to add a 1 in front of numbers in excel

  • Consequently, Excel will add the 1 to the number.
  • Finally, lower the cursor down to autofill the rest of the cells.

using ampersand operator to show how to add a 1 in front of numbers in excel

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.

choosing format cells option to show how to add a 1 in front of numbers in excel

  • In the prompt, go to Custom under Category.
  • Then, in the box under the Type option type,
1#
  • Finally, click OK.

typing formula to show how to add a 1 in front of numbers in excel

  • 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


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.

applying sum function to show how to add a 1 in front of numbers in excel

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.

using concatenate function to show how to add a 1 in front of numbers in excel

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.

applying numbervalue function to show how to add a 1 in front of numbers in excel

Read More: How to Concatenate and Keep Number Format in Excel


Similar Readings


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.

using if function to show how to add a 1 in front of numbers in excel

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.

using a combination of the left and the if functions to show how to add a 1 in front of numbers in excel

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

Md. Abdul Kader

Md. Abdul Kader

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

Leave a reply

ExcelDemy
Logo