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

## Method 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, with quotation marks around the 1 to add it to the number.

Steps:

• Select the C5 cell and type the following.
`="1"&B5`
• Hit Enter.

• Excel will add the 1 to the number.
• Drag the cursor down the column to autofill the rest of the cells.

## Method 2 – Utilizing Format Cells Option

Steps:

• Select the cell range C5:C12.
• Right-click and select Format Cells.
• A prompt box will be opened.

• Go to Custom under Category.
• In the box under the Type option type enter 1#
• Click OK.

• A 1 has been added to the numbers in those cells.

## Method 3 – Applying SUM Function

Steps:

• Select the D5 cell and enter the below formula.
`=SUM(B5+C5)`
• Hit Enter.

• The number 1 has been added to our existing number.
• Drag the cursor down the column to autofill the rest of the cells.

## Method 4 – Using CONCATENATE Function

Steps:

• Select the C5 cell and enter the following formula.
`=CONCATENATE(1,B5)`
• Press Enter.

• A 1 has been added in front of the number.
• Drag the cursor down the column to autofill the cells.

## Method 5 – Applying NUMBERVALUE Function

Steps:

• Select the C5 cell and enter the below formula.
`=NUMBERVALUE(1&B5)`
• Hit Enter.

• Drag the cursor down the column to autofill.

## Method 6 – Utilizing IF Function

Steps:

• Select the C5 cell and enter the following formula.
`=IF(B5<>"","1"&B5,"")`
• Press Enter.

• Drag the cursor down the column to autofill the remaining cells.

## Method 7 – Using Combination of LEFT and IF Function

If the number begins with zero then the previous methods won’t work, in which case this method is preferred.

Steps:

• Click on the C5 cell and enter the following formula.
`=IF(B5="","",(0+(LEFT(B5)="0")&B5)+0)`
• Press Enter.

• Drag the cursor down the column to autofill the cells.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!