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

You may have added numbers and data manually in an Excel sheet. But sometimes you may need to add a number in front of a number in Excel and it is not that smart to add a number manually when you got a datasheet of a large number of data. Moreover, it is very time-consuming. So, won’t it be great if you can automate the process by applying Excel functions and features? In this article, I will show you how to add a number in front of a number in Excel.


Download Practice Workbook


7 Simple Ways to Add a Number in Front of a Number in Excel

In this section, you will find 7 quick and suitable tricks to add a number in front of a number in an excel workbook. Let’s check them now!


1. Adding a Number with Double Quotes

Let’s say we have got a dataset of different products of a shop and their corresponding Product ID before the delivery of the product.

Dataset for Adding a Number in Front of a Number in Excel

Here, for this dataset, the Product ID will change with the delivery of the product. Let’s say the ID will change by adding a “1” at the beginning of the previous ID that was before delivery. So, for our dataset, we want to add “1’ before the numbers we got. You can proceed with the number you need.

So, for adding a number (“1”) before the numbers with double quotes, follow the steps below:

  • First of all, select a cell where you want the output and the following formula for the cell:

=”1”&C5

Here,

  • C5= the actual number which will have a number at the beginning

Add a Number in Front of a Number

  • Then, press ENTER & you will get the output.

  • Now, use the Fill Handle tool to drag the formula and you will see the result.

Adding a number in front of a number in Excel

So easy, isn’t it? You can add a number in front of a number in the blink of an eye!


2. Entering a Number Before a Number Using Format Cells

Let’s say, for our previous dataset, we want to add a number (“1”) in front of the product ID before delivery by using the Format Cells option.

For doing so, just follow the steps below:

  • Firstly, copy the actual numbers to another column.

  • Then, right-click the mouse> select Format Cells.

Adding a Number in Front of a Number in Excel

  • Hence, the Format Cells dialogue box will show up. From the Number icon, select Custom> type 1# in the Type box> click OK.

Adding a Number in Front of a Number in an Excel worksheet

  • Finally, you will see the output.

Add a Number in Front of a Number in Excel

In this way, you can utilize the format cells option for adding a number at the beginning of a number.


3. Using NUMBERVALUE Function to Add a Number

For the same dataset and for adding the same number (“1”), let’s apply the NUMBERVALUE function.

Proceed with the following steps for this method:

  • Firstly, select a cell and apply the following formula:

=NUMBERVALUE(1&C5)

Here,

  • C5= the actual number which will have a number at the beginning

  • Now, hit ENTER and the cell will show the result.

Add a Number in Front of a Number in Excel

  • After that, drag the formula for the cells you want the output.

By applying the NUMBERVALUE function, you can add numbers in this way.


4. Add a Number in Front of a Number by Using IF function

We will now the application of the IF function for adding a number(“1”) for our same datasheet. Chase the steps below for doing so:

  • First of all, apply the following formula to a selected cell:

=IF(C5, “1”&C5, “”)

Here,

  • C5= the actual number which will have a number at the beginning

  • Then, hit ENTER and the cell will show the result.

  • After that, drag the formula for the rest of the cells.

In this way, you can apply the IF function for adding a number at the start of another number.


5. Use CONCATENATE Function to Add a Number at Beginning

Here, you will see the use of CONCATENATE function for entering a number at the beginning of another number. We will use the same dataset and will add the same number (“1”).

Proceed with the following steps for applying this function:

  • At first, select a cell and apply the following formula to the cell:

=CONCATENATE(1, C5)

Here,

  • C5= the actual number which will have a number at the beginning

  • After that, hit ENTER and drag the formula to the down cells to get the same result.

So, this function also allows you to add a number before another number in Excel.


6. Add a Number in Front of a Number by Summing Extra Number

For the same dataset, if we want to add “1” in front of the ID number, we will now add an extra number and then sum it to the actual number for adding “1” at the beginning.

For example, if you have a 3-digit number and you want to enter 1 at the start, then you have to sum 1000 to the actual number.

For putting 2 at the beginning of an 8-digit number, sum 200000000.

In our dataset, we have 8-digit numbers and we want to add “1” at the beginning. So, we need to sum 100000000 to our actual number.

Now, let’s start the procedure for our dataset:

  • First of all, add a column containing 100000000 for summing.

  • Then apply the following formula to the selected cell:

=C5+D5

Here,

  • C5= the actual number which will have a number at the beginning
  • D5= the number to add (100000000)

  • Here, you can also apply the following formula (both formulas will return the same value):

=SUM(C5:D5)

  • Now, hit ENTER and drag the formula to the down cells to get the output.

In this way, you can also add a number at the beginning.


7. Adding a Number at Beginning of a Number Starts with Zero

Let’s say, we have a dataset containing 0 at the start and we want to enter “1” at the beginning.

For entering “1” at the beginning, we will use IF & LEFT functions for this method.

Now, proceed with the steps below:

  • First of all, select a cell and apply the following formula:

=IF(C5=“”, “”, (0+(LEFT(C5)=“0”)&C5)+0)

  • C5= the actual number which will have a number at the beginning

  • Then, press ENTER, and the cell will show the output.

  • Now, drag the formula to the down cells to get the output.

These are the steps you can follow to add a number in front of a number.


Conclusion

In this article, you have learned how to add a number in front of a number in Excel. I hope from now on, you can easily add a number in front of a number in Excel. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo