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.
="1"&B5
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.
=SUM(B5+C5)
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
CONCATENATE (text1, text2, [text3], ...)
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
=CONCATENATE(1,B5)
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
=NUMBERVALUE (text, [decimal_separator], [group_separator])
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.
=NUMBERVALUE(1&B5)
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
=IF (logical_test, [value_if_true], [value_if_false])
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.
=IF(B5<>"","1"&B5,"")
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
=LEFT (text, [num_chars])
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
=IF(B5="","",(0+(LEFT(B5)="0")&B5)+0)
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)