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.
- Adding a number with & operator and double quotes
- Using format cells to add a number before a number
- Using the NUMBERVALUE function
- Using the IF function
- Apply the CONCATENATE function
- 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.
