Jonayed
Sometimes we need to add thousand separator (,) in Excel formula. In this scholarly, I will show you how you can add thousand separator (,) in the Excel formula with 2 easy approaches. I have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download free workbook from here:
Learn to Add Thousand Separator (,) in Excel Formula with These 2 Approaches
The third digit from the right can be followed by a comma in numbers with 4 digits or more (i.e., numbers exceeding 999). These “thousands of separators” make it easier to read the entire numbers since we can immediately identify where the different groups of characters fall.
While writing this article, we considered a dataset that contains approximately 6 columns and 13 rows. In this dataset, we added Items, Quantity, Unit Charge, Total Charge, and With Separator to make you better understand. But if you want, you can change the entities and put your own values.
1. Adopting TEXT Function Beyond Decimal Value
You can use the TEXT function to add thousand separator (,) in Excel formula. It is a very simple way. You can do this with no difficulty. The structure of that function is like this:
=TEXT(value,format_text)
🔨 Formula Breakdown
Here:
👉 The value refers to the adjacent cell value where the separator will be inserted. In this article, the value will be cell E6.
👉 The format_text is an expression that specifies the formatting we want to apply to the provided value in text form.
You can do this by maintaining the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you have to select cell F6.
- Secondly, write the following thing on that cell.
=TEXT(E6,”#,####”)
- Thirdly, press Enter.
- Afterward, use the fill handle to drag the formula to the entire column.
- Finally, you will get the desired result you wanted to have.
Truly, this approach is very handy to add thousand separator (,) in an Excel formula.
📕 Read More: 6 Easy Ways to Put Comma After 5 Digits in Excel
2. Using TEXT Function with Decimal Value
The above approach is for the no decimal value. Now if you want can add a Decimal value to add thousand separators (,) in the Excel formula. It is also very easy and handy. Follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you have to select cell F6.
- Secondly, write the following thing on that cell.
=TEXT(E6,”#,####.00”)
- Thirdly, press Enter.
- Belatedly, you need to use the fill handle to drag the formula to the entire column.
- Finally, you will get the required result you wanted.
Undoubtedly, this approach is the easiest approach to adding thousand separator (,) in the Excel formula.
📕 Read More: 10 Ways to Put a Comma After 3 Digits in Excel
How to Add Commas with Formula in Excel
If you are looking for a formula to add commas in Excel then this portion is especially for you. We have used a very unique function (TEXTJOIN) to achieve this. You can also do this easily if you follow the below steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you have to select cell D6.
- Secondly, write the following formula on that cell.
=TEXTJOIN(“,”,TRUE,B6:B13)
- Thirdly, tap Enter.
- Consequently, your required result will appear to you.
Truly, by following the above procedure you can easily add commas in Excel.
📕 Read More: 7 Easy Ways to Use Comma in Excel Formula
How to Apply Thousand Separator (,) Format in Excel
In this part, we are going to discuss the thousand separators (,) format in Excel. It is not a difficult way to achieve. You can simply do this by maintaining the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the entire E column.
- Secondly, go to the Home tab.
- Thirdly, click on the Number Format.
- Now you will get a dialogue box name Format Cells.
- Fourthly, select Number.
- Fifthly, check the Use 1000 Separator(,).
- Eventually, you will see the result as well.
Indeed, this approach is very handy if you are looking for thousand separator (,) format in Excel.
📕 Read More: 9 Tricks to Put Comma After 2 Digits in Excel
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You need to be careful while using the TEXT function.
🖊️ You should be conscious about applying the thousand separator (,) format.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Initially, we applied the TEXT function to add thousand separators (,) in the Excel formula.
📌 Secondly, we tried to show you how to add commas with formulas in Excel.
📌 Finally, we showed you the thousand separators (,) format in Excel.
Conclusion
We wish that you would be able to add thousand separator (,) in the Excel formula using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any confusion in your mind, feel free to ask me through the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 5 Tricks to Insert Comma Between Words in Excel
- 5 Easy Ways to Add Comma in Excel to Concatenate Cells
