MD Tanvir Rahman
Inserting digits, letters, or text before an existing number or text is an essential task while creating an employee or student ID. Often you might notice that there are one or two digits of code before a phone number which represents a specific country or region. If you are having trouble adding a specific digit before an existing number, you are on the right track. This article is the perfect place for you. In this article, we will learn to add a number in front of a number in Excel.
Say, you need to add 99 before every phone number located in the dataset. Using the Format Cells option from the Context Menu you will be able to add 99 before every existing number. In contrast, using 7 different formulas can be another option to put 99 at the beginning of a number. The use of VBA Macro is also explained later in this article.
📁 Download Excel File
To practice please download the Excel file from the link below:
Learn to Add a Number in Front of a Number in Excel with These 9 Approaches
In this article, we are going to expound on 9 quick approaches to add a number in front of a number in Excel. Here we consider a dataset titled Contact List of Hotel California containing Name, Designation, and Phone Number. The dataset has 4 columns as well as 14 rows. To construct the methods, we determine to construe the use of IF, LEFT, CONCATENATION, TEXTJOIN, and NUMBERVALUE functions. The use of the Ampersand operator, Helper column, and VBA Macro is also illustrated later. So, let’s get started.
1. Utilize Excel Ampersand Operator to Put Numbers in Front of Number
Prominently the Ampersand (&) operator connects the texts or numbers. Using the Ampersand operator, one will be able to add numbers in front of the number. In this method, we will show you to add two digits 9,9 at the beginning of the number. Please follow the necessary steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select a cell i.e. E6 under the Outcome heading.
- Secondly, insert the following formula in the E6 cell containing the Ampersand operator.
=”99″ &D6
- Thirdly, the Ampersand operator adds 99 at the beginning of the number located at the D6 cell.
- Finally, obtain the result 999083564879 in the E6 cell. Using the Fill-Handle tool, autofill the rest cells of the Outcome column.
2. Use Format Cells Option to Add Numbers in Front of a Column in Excel
Unlike using any formula in the cells, one can add any digits in front of a number using the Format Cells option from the Context Menu list. Phone number formatting as well as any number can be done in this way. Please follow the required steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select the range E6:E14 containing numbers.
- Secondly, Right-Click on Mouse to get the Context Menu list.
- Thirdly, select the Format Cells option from the Context Menu list.
- Thus a pop-up box named Format Cells appears.
- Then, select Custom from the Category list.
- Next, write 99# in the Type field. Consequently, observe the data in the Sample field and hit on the Ok button.
- Finally, get the output 999083564879 in the E6 cell under the Outcome heading where 99 is added automatically at the beginning of the number. Auto-Filling the remaining cells obtain the Outcome column.
3. Incorporating IF Function to Add Two Extra Numbers in Front of Number in Excel
IF is a Logical function where it decides output based on a logic test. Using the IF function, one can easily insert numbers in front of a number in Excel. Please check the general Syntax of the IF function as follows,
=IF(Logical Text, Value_if_TRUE, Value_if_FALSE)
Please check the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a cell i.e. E6 under the Outcome heading.
- Secondly, write down the code in the E6 cell containing the IF function.
=IF(D6, “99”&D6, “”)
- Here, the IF function takes the value from the D6 cell, and if it is true then it joins 99 at the beginning of the number.
- Therefore, obtain the result 999083564879 in the E6 cell and use Fill-Handle to autofill the rest of the Outcome column.
4. Implement NUMBERVALUE Function to Add Numbers to Existing Cell Value
Excel has a function named NUMBERVALUE that deals with the number only. NUMBERVALUE function adds digits with a number at any position. Please check the general syntax of NUMBERVALUE function as follows,
=NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])
Please check out the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, navigate a blank cell i.e. E6 in the Outcome column.
- Secondly, input the code in the E6 cell containing the NUMBERVALUE function.
=NUMBERVALUE(99&D6)
- Here, the NUMBERVALUE function takes the value from the D6 cell and joins 99 using an Ampersand(&) at the beginning of the number.
- Therefore, obtain the result 999083564879 in the E6 cell and use the Fill-Handle tool to autofill the rest of the Outcome column.
5. Employ CONCATENATE Function to Add in Front of Phone Numbers
CONCATENATE function not only joins the texts of two cells but also joins a random number and a cell from the dataset. Using the CONCATENATE function one can insert digits before a number. CONCATENATE function is also known as the CONCAT function. Please follow the CONCATENATE functions below.
=CONCATENATE(text1, [text2], …)
Please check out the required steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select a cell i.e. E6 from the Outcome column.
- Secondly, insert the following formula in the E6 cell containing the CONCATENATE function.
=CONCATENATE(99,D6)
- Here, the CONCATENATE function takes the value from the D6 cell and adds 99 in front of the number.
- Therefore, achieve the result 999083564879 in the E6 cell.
- Lastly, to fill the Outcome column automatically, use the Fill-Handle tool.
6. Add Two Digits Before a Number Using TEXTJOIN Function in Excel
Like the CONCATENATE function, there is another function called the TEXTJOIN function that is used to join texts of different cells as well as numbers. Utilizing the TEXTJOIN function, one can add digits before a number within a minute. Please follow the syntax of the TEXTJOIN function below,
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Please check out the necessary steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, navigate a blank cell i.e. E6 from the Outcome column.
- Secondly, input the formula in the E6 cell having the TEXTJOIN function.
=TEXTJOIN(“”,1,99,D7)
- Here, the TEXTJOIN function considers the logic and if the logic is True then it picks up the value from the D6 cell and adds 99 in front of the number.
- Therefore, obtain the result 999083564879 in the E6 cell.
- In the end, to fill the Outcome column automatically, drag the Fill-Handle tool till the last E14 cell.
7. Combine Excel LEFT and IF Functions to Put Number in Front of Numbers Starting with 0
Suppose one needs to add a single number before a particular number for example zero. Applying the LEFT and IF functions together, can be another key to putting a number in front of numbers that start with 0. The LEFT function generally counts the string from the beginning while the IF function is a logical function. Please check the general syntax of the LEFT function below,
=LEFT(text, [num_chars])
Here,
Text= Required. The text or number string that we are determined to extract the characters.
Num_chars = Optional. Specified by LEFT to extract.
Please check out the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Similarly, select a blank cell i.e. E6 under the Outcome heading.
- Then, write the following formula in the E6 cell including the combination of LEFT and IF functions.
=IF(D6=””, “”, (0+(LEFT(D6)=”0″)&D6)+0)
🔨 Formula Breakdown
👉 Firstly, the LEFT function finds out the character 0 at the beginning of the number.
👉 Secondly, the IF function joins 1 before every Phone Number that starts with 0 in the Outcome column.
- Therefore, achieve the result 109083564879 in the E6 cell. To fill the rest of the Outcome column automatically, use the Fill-Handle tool.
8. Add Two Additional Numbers at Beginning of Number Using Helper Column
Like the previous 7 methods, inserting one or two numbers before a number with a Helper column will be your cup of tea. Adding a suitable number i.e. 99000000000 that remains in the first position, you can sum up with the Phone Number column. Please follow the required steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert a Helper column that we determine to add with the Phone Number column.
- Then, Like previously, select a cell i.e. F6 after the Outcome heading.
- Next, write down the formula in the F6 cell.
=D6+E6
- Therefore, adding the Phone Number by 990000000000, get the result 999083564879 in the E6 cell.
- In the end, Use the Fill-Handle tool to fill the rest of the Outcome column automatically.
9. Utilize Excel VBA Macro to Put Two 0 Including Apostrophe in Front of Numbers
Using a simple VBA code anyone can insert numbers or apostrophes before an existing number. But applying VBA Macro is not everyone’s mug of coffee. Here, we will learn the steps to insert two zeros along with an apostrophe before an existing number. To learn the procedure, please follow the necessary steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.
- Thirdly, click on the Developer option and hit the OK button.
- Now Developer mode is on.
- Then, click on the Visual Basic feature.
- Next, open the dedicated worksheet VBA or the Module from the Insert menu.
- After that, insert the following VBA code in the dedicated worksheet VBA and hit the OK button.
Code
Sub Add_Number_in_Front_of_a_Number()
Dim i As Integer
For i = 1 To 9
Cells(5 + i, 5).Value = “‘00” & Cells(5 + i, 4).Value
Next i
End Sub🔨 Code Breakdown
👉 Firstly, writing Cells(5 + i, 4).Value collects the cell value from the D6 cell where (i+5,4) is respectively 6th row and 4th column.
👉 Then Cells(5 + i, 5).Value outline the result containing an apostrophe and double zero which is connected by an ampersand operator.
👉 Next, since we use For loop, we continue the process till the value of i meets.
- Finally, achieve the result ‘009083564879 in the E6 cell including an apostrophe and two zeros at the beginning of the number using VBA Macro. Similarly, we obtain the result in the Outcome column automatically.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ Remember the TEXTJOIN function is only available in 2019 or Higher Versions.
🖊️ NUMBERVALUE function only deals with Numbers.
📝 Takeaways from This Article
📌 Ampersand operator to add numbers at the beginning of a number.
📌 Format Cells option to format the number based on number style.
📌 Combination of IF and LEFT functions along with only IF function to add digits before the number.
📌 NUMBERVALUE function to put digits before a number.
📌 CONCATENATE function to join digits with a number.
📌 TEXTJOIN function to join text as well as numbers.
📌 Helper column to add numbers with an existing number.
📌 VBA Macro to use as an average counting calculator.
Conclusion
In this article, we expound on 9 handy approaches to adding a number in front of a number in Excel. I hope you enjoyed your learning and will be able to insert digits at the beginning of a number. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.
