How to Add a 1 in Front of Numbers in Excel [7 Special Tricks]

You may attach a special type of number like 1 before the local phone number of the US to make it an international identity. It is also mentionable that the country code for the US is +1.

In this article, I’ll discuss seven special tricks to add 1 in front of numbers in Excel.

Download Practice Workbook

How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. At last, press Enter.

Furthermore, you can use the Fill Handle Tool (appears in the lower right of the selected cell as a small square) to copy the formula for other cells.

How to Add a 1 in Front of Numbers in Excel

We see a data set in the following figure where US local area phone numbers are given. Right now, we have to make these numbers international phone numbers by adding 1.

Let’s get started.

1. Entering 1 with Double Quotes (“1”)

It is one simplest and quick way to add 1 before numbers.

Just insert the following formula.

Here, B5 is the local area phone number.

2. Using Format Cells

Format cells are a collection of different types of formats where we can convert easily the appearance without modifying the number.

You can easily utilize these options. For this, just follow the steps.

Select the data (C5:C12)>press Ctrl+1>Choose Custom option>type 1# > press Ok

Note. Ctrl+1 is the keyboard shortcut for opening Format Cells.

After following the procedure, you’ll get the output like the following

3. Summing Numbers

You can sum some extra with the US local area phone number.

For example, if any number is 3 digits, you have to sum 1+000 (input 3 times zero) with the number.

As our number consists of 10 digits, we need to add 1+0000000000 (input 10 times zero).

And sum up this number with local phone number.

Also, you can use the SUM function.

Here, B5 is the US local phone number and C5 is the number to add.

4. Using CONCATENATE Function

Let’s imagine, you need a formula for adding 1 before the number.

In that case, CONCATENATE will be a good choice.

This function combines two or more values to one value as text.

That’s why 1 will be shown at first of the numbers.

The syntax of the function is

The arguments are the following.

text1 – The first text value to join together.

text2 – The second text value to join together.

For our data set, the formula will be

Here, B5 is the US local phone number.

5. Using NUMBERVALUE Function

The NUMBERVALUE function turn value into a number with an individual separator.

We may easily employ this function in our data set. The syntax of the function is

The arguments are below.

text – The text to convert to a number.

decimal_separator – [optional] The character for decimal values.

group_separator – [optional] The character for grouping by thousands.

The formula takes the following form in the case of our dataset.

Here, B5 is the US local phone number.

6. Using IF Function

IF function can be used for various purposes. We’ll use the function in our dataset. The syntax of the function is

There are following arguments in the function.

logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true – [optional] The value to return when logical_test evaluates to TRUE.

value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

For getting our expected output, we may modify the IF function in the following ways.

Here, B5 is the US local phone number.

7. When the Number Starts with Zero

Imagine, the number starts with zero or has leading zeros. Now, if you want to apply the earlier tricks, you won’t get the expected result. Because number with leading zero is only available completely in Excel when it stores as text.

In this trick, we’ll use the combination of IF and LEFT functions for our dataset.

The syntax of the LEFT function is

The arguments are below

text – The text from which to extract characters.

num_chars – [optional] The number of characters to extract, starting on the left side of the text.

The formula for our data set will be

Here, B5 is the cell of a local phone number

Note. In the screenshot, we see small green triangle which indicates that the number stores as text.

Conclusion

This is how you can add a 1 in front of numbers quickly. If you have any opinions, please share them below in the comments section.

Thanks for being with me.

Further Readings

• Concatenate Numbers in Excel (4 Quick Formulas)
• Combine Multiple Cells Into One Cell Separated By Comma In Excel
• How to Combine Date and Text in Excel (5 Ways)
• Concatenate Date and Time in Excel (4 Formulas)
• Combine Multiple Columns into One Column in Excel
• Opposite of Concatenate in Excel (4 Options)
• How to Concatenate Two Columns In Excel (5 Methods)