How to add a number in front of a number in Excel – 6 easy methods

A square with a green color and the letter "x" displayed on it.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you’re looking to add a number in front of a number in Excel, we’ve got just the guide for you.

Let’s say you have a dataset of local phone numbers. Now, you want to make a separate list with the international code added to the data. Manually adding a number at the start would be super tedious, and not particularly efficient either!

You can add a number in front of a number in Excel in more ways than one, so we’ve gone ahead and shared six approaches we think are worth trying out. Let’s dive right in!

How to add a number in front of a number in Excel

Here’s the scenario – we have a sample dataset of US numbers without their international dialing code: 1. 

Let’s explore the following six ways to add (1) in front of each phone number in Excel. 

  1. Adding a number with & operator and double quotes
  2. Using format cells to add a number before a number
  3. Using the NUMBERVALUE function
  4. Using the IF function
  5. Apply the CONCATENATE function
  6. By summing extra number using REPT & LEN functions   

Method 1 – Adding a number with & operator and double quotes

We can use the & (AND) operator to join 1 at the beginning of each phone number. To proceed: 

1. Choose a cell where you want the output and then enter the following formula:

={Number You Want to Add in Double Quotes}&{Cell Address Where You Want to Add}

=”1″&B6 

2. Hit Enter to see the results.

3. Drag the Fill Handle tool to input the formula in the remaining cells.    

That was the easiest way to add a number in front of a number in Excel.

Method 2 -Using format cells to add a number before a number

This method involves using the Format Cells dialogue box to add a number at the beginning of a number.  

1. Copy & paste the list of numbers to the output column.

2. Select the column, press the Format drop-down, and choose Format Cells…   

Expert tip: Use the CTRL+1 shortcut to open the Format Cells dialogue box directly.

3. On the default Numbers tab, go to the Custom side menu and enter 1# under the Type: input box. Then, click OK.

4. Voila! The numbers are added in front of the numbers.

Method 3 – Using the NUMBERVALUE function

The NUMBERVALUE function converts text to a number in a locale-independent way. Here’s how to utilize it for our scenario: 

1. Choose the output cell and enter the following formula:

=NUMBERVALUE( {Number You Want to Add} & {Cell Address Where You Want to Add} )

=NUMBERVALUE(1&B6)

2. Click Enter to see the result.

3. Again, drag the cell containing the formula down the lane using its bottom-right corner to see results in other cells.  

Method 4 – Using the IF function

Let’s use the logical comparison power of the IF function to insert 1 at the beginning of our US numbers. 

1. Employ the following formula in a cell:

=IF( {Cell Address Where You Want to Add}, {“Number You Want to Add”} & {Cell Address Where You Want to Add}, “” )

=IF(B6,”1″&B6,””)

2. Hit Enter to execute the formula and check the result.  

3. Drag the cell to the rest of the cells to apply the changes.

Method 5 – Apply the CONCATENATE function

Sharing similarity with Method 1, the CONCATENATE or CONCAT function lets you combine two or more elements. 

1. Choose a cell and apply the following formula:

=CONCATENATE( {Number You Want to Add}, {Cell Address Where You Want to Add} )

=CONCATENATE(1,B6) 

Side note: You can use CONCAT instead of CONCATENATE

2. To apply and see the outcome, click Enter.

3. Lastly, drag down the cell with the formula to apply it to other cells.

Method 6 – ASumming extra number using REPT & LEN functions

There’s a neat trick we can use to add a number of our choice at the start of a number. That’s by summing the extra number.

Let’s say we have a 2-digit number, 10, and we want to add 1 at its start. 

For that, we have to take a 3-digit number starting with the number (we want to add) and trailing zeros – 100.

After summing the two numbers, 100 & 10, we get 110. To achieve this, we’ll use REPT and LEN functions.   

1. Apply the following formula:

= {Number You Want to Add} & REPT(“0”, LEN(Cell Address Where You Want to Add)) + Cell Address Where You Want to Add

=1&REPT(“0”,LEN(B6)) + B6

2. Hit Enter to check out the output.

3. Drag the formula across the relevant cells to apply it to them.

Side note: I have deleted one number in B7, but the formula is still working – adding a number at its start.      

Wrapping Up

Above, we laid out six ways to add a number in front of a number in Excel. The easiest one is using the & operator. But if you want a dynamic & smart method, I suggest you use the last way.

For more guides to everything Excel, be sure to check back in with us soon.

   

Ramzi Musa is a Growth Manager and Editor for BGFG. He has experience and expertise in tech, gaming, and hardware.