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.

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


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.


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


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


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.

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


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


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.

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


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


<< Go Back to Concateante Numbers in Excel | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo