2 Easy Ways to Add Thousand Separator in Excel Formula

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.

Sample dataset containing price list of different items

APPROACH

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.

Applying the TEXT function in the dataset

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.

Adding thousand separator using the TEXT function

  • Afterward, use the fill handle to drag the formula to the entire column.
  • Finally, you will get the desired result you wanted to have.

The result after using the TEXT function to add thousand separator

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

APPROACH

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.

Adding thousand separator using the TEXT function with decimal value

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

Result after using the TEXT function to add thousand separator with decimal value

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.

Using the TEXTJOIN function to add commas

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 column.
  • Secondly, go to the Home tab.
  • Thirdly, click on the Number Format.

Selecting Number Format from the Home tab

  • Now you will get a dialogue box name Format Cells.
  • Fourthly, select Number.
  • Fifthly, check the Use 1000 Separator(,).

Checking 1000 separator from the Format Cells

  • Eventually, you will see the result as well.

Result after using the thousand separator format in Excel

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

(Visited 62 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo