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.

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

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

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

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

• Press Enter.

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

Read More: How to Concatenate Cells with If Condition in Excel

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.

• Press Enter.

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

Download Practice Workbook

Related Articles

• How to Combine Text and Number in Excel
• How to Concatenate Numbers in Excel
• How to Concatenate Numbers with Leading Zeros in Excel
• How to Add Leading Zeros in Excel by CONCATENATE Operation
• How to Concatenate and Keep Number Format in Excel
• How to Concatenate Date That Doesn’t Become Number in Excel
• How to Combine Date and Text in Excel
• How to Concatenate and Keep Currency Format in Excel
• How to Combine Text and Numbers in Excel and Keep Formatting

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