Sometimes you may need to add digits to a number in Excel. In this article, I will explain how to add digits to a number in Excel.
How to Add Digits to a Number in Excel: 8 Easy Methods
Here, I will describe 8 methods to add digits to a number in Excel. Also, I will show how to add digits to a number not only at starting and ending but also at the middle in Excel. For your better understanding, I will use a sample dataset. Which contains 2 columns. Those are Customer Name and Customer ID. The dataset is given below.
1. Use of Ampersand(&) Operator to Add Digits a Number in Excel
You can use the Ampersand (&) operator to add digits in a Number both at the starting and ending in Excel. Now, I’ll show you how to use the Ampersand (&) operator.
1.1. Adding Digits in Front of a Number in Excel
When you need to add digits in front of a number in Excel, you can follow the given steps.
Steps:
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
="10"&C5
Formula Breakdown
- In the formula, I have taken 10 to add these digits in front of the number. That’s why I have to write it in an Inverted Comma like “10”.
- Ampersand (&) operator will join 10 to the number.
- Subsequently, you must press ENTER to get the result.
- After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at starting.
1.2. Adding Digits at End to a Number in Excel
When you need to add digits at the end of a number in Excel, you can follow the given steps.
Steps:
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=C5&"10"
Formula Breakdown
- In the formula, I have taken 10 to add these digits at the end of the number. That’s why I have to write it in an Inverted Comma like “10”.
- Ampersand (&) operator will join 10 to the number at the end.
- Now, you have to press ENTER to get the result.
- After that, you can double-click to the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at the ending.
Read More: How to Add Number to Existing Cell Value in Excel
2. Using CONCATENATE Function
There is a built-in function in Excel to add digits or text to any text or number. Which is the CONCATENATE function. So, you can use the CONCATENATE function to add digits in a number both at the starting and ending in Excel. The steps are given below.
Steps:
Here, I will add the digits at the end of a number.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=CONCATENATE(C5,"10")- Finally, you have to press ENTER to get the result.
Formula Breakdown
- Here, the CONCATENATE function will join several texts to one text.
- The Inverted Comma (“10”) denotes that 10 is a string.
- So, CONCATENATE will join 161201 and 10.
- Output: 16120110
- After that, you can double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at the ending.
Similarly, you can add digits in front of a number in Excel by using the CONCATENATE function.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D15 cell.
- Secondly, use the corresponding formula in the D15 cell.
=CONCATENATE("10",C15)- Finally, you have to press ENTER to get the result.
Formula Breakdown
- Here, the CONCATENATE function will join several text to one text.
- The Inverted Comma (“10”) denotes that 10 is a string.
- So, CONCATENATE will join 10 and 161201.
- Output: 10161201
- At this time, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D16:D20.
Finally, you will see the add number in front of the number.
3. Applying NUMBERVALUE Function and Ampersand(&) Operator
Also, you can use the NUMBERVALUE function and Ampersand(&) Operator to add digits in a Number both at the starting and ending in Excel. The steps are given below.
Steps:
Here, I will add the digits at the end of a number.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=NUMBERVALUE(C5&"10")
- Finally, you have to press ENTER to get the result.
Formula Breakdown
- Here, the NUMBERVALUE function will convert any text to a number format.
- So, C5 will be the text.
- The Inverted Comma (“10”) denotes that 10 is a string.
- Then, Ampersand (&) will join 161201 and 10.
- Finally, NUMBERVALUE will convert those strings to the number.
- Output: 16120110
- After that, you can double click to the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at the ending.
Similarly, you can add digits in front of a number in Excel by using the NUMBERVALUE function.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D15 cell.
- Secondly, use the corresponding formula in the D15 cell.
=NUMBERVALUE("10"&C5)- Finally, you have to press ENTER to get the result.
Formula Breakdown
- Here, the NUMBERVALUE function will convert any text to a number format.
- The Inverted Comma (“10”) denotes that 10 is a string.
- Then, Ampersand (&) will join 161201 and 10.
- Finally, NUMBERVALUE will convert those strings to the number.
- Output: 10161201
- At this time, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D16:D20.
Finally, you will see the added digits in front of the number.
4. Employing Format Cells Command to Add Digits to a Number in Excel
You can employ the Format Cells command to add digits not only in front of a number but also at the ending of a number in Excel. The steps are given below.
Steps:
Here, I will add the digits at the end of a number.
- Firstly, you have to select the data range. Here, I have selected the range C5:C10.
- Secondly, from the Home tab >> you need to go to the Cells feature.
- Thirdly, from the Format command >> you have to choose the Format Cells command.
At this time, a dialog box named Format Cells will appear.
- Now, from that dialog box, you have to make sure that you are on Number command.
- Then, you need to go to the Custom option.
- After that, you have to Type your required digits with a Hashtag (#) at the beginning of the digits. Here, I have typed #15. Also, you can show the written sample instantly in the Sample box.
- Finally, you must press OK to get the result.
Lastly, you can see the added digits at the end of the number.
Now, I will add the digits in the front of a number.
- Firstly, you have to select the data range. Here, I have selected the range C15:C20.
- Secondly, you need to Right-Click on the data to open the Context Menu Bar.
- Finally, from the Context Menu Bar, you need to choose the Format Cells option.
At this time, a dialog box named Format Cells will appear.
- Now, from that dialog box, you have to make sure that you are on the Number command.
- Then, you need to go to the Custom option.
- After that, you have to Type your required digits with a Hashtag (#) at the end of the digits. Here, I have typed 1000000#. In addition, if you want to add a zero (0) then you must type 6 zero. That means 5 zero is constant to include 1 zero. Also, you can show the written sample instantly in the Sample box.
- Finally, you must press OK to get the result.
Finally, you will see the added digits at the beginning of the number.
5. Use of Format Cells Feature to Add Digits in Middle of a Number
You can use the Format Cells Command also to add digits at the middle of a number in Excel. The steps are given below.
Steps:
- Firstly, you have to select the data range. Here, I have selected C5:C10.
- Now, you need to press CTRL+1 keys to open the Format Cells dialog box directly. In the previous method you can use these keys for shortcuts.
At this time, a dialog box named Format Cells will appear.
- Now, from that dialog box, you have to make sure that you are on the Number command.
- Then, you need to go to the Custom option.
- After that, you have to Type your required digits with a Hashtag (#) at the beginning of the digits. Also, you need to include the zeros. Basically, zero denotes the position of the written digits from the end of the number. Here, I have typed #55000. Which means 55 will be in the 4th position from the rightmost part. Also, you can show the written sample instantly in the Sample box.
- Finally, you must press OK to get the result.
Finally, you will see the added digits at the middle of the number.
6. Use of LEFT Function and Ampersand(&) Operator
You can use the LEFT function and Ampersand(&) Operator to add digits in a Number both at the starting and ending in Excel. The steps are given below.
Steps:
Here, I will add the digits at the end of a number.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=LEFT(C5,6)&"10"- Finally, you have to press ENTER to get the result.
Formula Breakdown
- LEFT(C5,6)—->Here, the LEFT function will count the leftmost 6 characters from cell C5.
- Output: 161201
- “10”—-> Inverted Comma will represent 10 as a string.
- & —-> Ampersand (&) will join 161201 and 10.
-
- Output: 16120110
- After that, you can double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at the ending.
Similarly, you can add digits in front of a number in Excel by using the LEFT function.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D15 cell.
- Secondly, use the corresponding formula in the D15 cell.
="10"&LEFT(C15,6)- Finally, you have to press ENTER to get the result.
Formula Breakdown
- “10”—-> Inverted Comma will represent 10 as a string.
- LEFT(C15,6)—->Here, the LEFT function will count the leftmost 6 characters from the cell C15.
- Output: 161201
- & —-> Ampersand (&) will join 10 and 161201.
-
- Output: 10161201
- At this time, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D16:D20.
Finally, you will see the added digits in front of the number.
7. Employing MID Function and Ampersand(&) Operator
Here, you can use the MID function and Ampersand (&) operator to add digits in a Number both at the starting and ending in Excel. The steps are given below.
Steps:
Here, I will add the digits at the end of a number.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=MID(C5,1,6)&"10"- Finally, you have to press ENTER to get the result.
Formula Breakdown
- MID(C5,1,6)—-> The MID function will extract 6 characters starting from the 1st character in cell C5.
- Output: 1612010
- “10”—-> Inverted Comma will represent 10 as a string.
- & —-> Ampersand (&) will join 161201 and 10.
- Output: 16120110
- After that, you can double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the number with added digits at the ending.
Similarly, you can add digits in front of a number in Excel by using the MID function.
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D15 cell.
- Secondly, use the corresponding formula in the D15 cell.
="10"&MID(C15,1,6)- Finally, you have to press ENTER to get the result.
Formula Breakdown
- “10”—-> Inverted Comma will represent 10 as a string.
- MID(C15,1,6)—-> The MID function will extract 6 characters starting from the 1st character in the cell C15.
- Output: 1612010
- & —-> Ampersand (&) will join 10 and 161201.
- Output: 10161201
- At this time, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D16:D20.
Finally, you will see the added digits in front of the number.
8. Applying LEFT and MID Functions with Ampersand(&) Operator
Interestingly, you can use the combination of functions with an operator to add digits in the middle of a number. Now, let’s see the use of the LEFT and MID functions with the Ampersand (&) operator to add digits at the middle of a number in Excel. The steps are given below.
Steps:
- Firstly, you have to select a cell, where you want to keep the changes. I have selected the D5 cell.
- Secondly, use the corresponding formula in the D5 cell.
=LEFT(C5,3)&"55"&MID(C5,4,3)- Finally, you have to press ENTER to get the result.
Formula Breakdown
- LEFT(C5,3)—->Here, the LEFT function will count the leftmost 3 characters from cell C5.
- Output: 161
- “55”—-> Inverted Comma will represent 55 as a string.
- MID(C5,4,3)—-> The MID function will extract 3 characters starting from the 4th character in the cell C5.
- Output: 201
- & —-> Ampersand (&) will join 161, 55, and 201.
-
- Output: 16155201
- After that, you can double click to the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see the added digits at the middle of the number.
Things to Remember
- Here, with the given methods, you can add any digit numbers (like 2/3/4… digits as your requirement) to a number.
- Furthermore, with the Format Cells command, you can’t add zero (0) at the end of a number.
- In the case of adding digits at the middle of a number, you have to use either the Format Cells command or LEFT, MID functions with the Ampersand operator.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 8 different ways to add digits to a number in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
<< Go Back to Add to Cell Value | Sum in Excel | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
