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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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.

**method**

### 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**.