How to Remove Comma in Excel Using Formula (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Commas have a variety of applications while working with Excel spreadsheets. They are quite useful too. But there are certain situations when you need to eliminate the extra commas from the Excel worksheet. In this article, I will show you two different methods for how to remove comma in Excel using formula. Hence, read the article properly and save time.

In this article, I have discussed two simple approaches to removing commas in Excel using formulas. It includes the SUBSTITUTE function and the NUMBERVALUE function. Moreover, I have provided some fantastic examples and explanations to help you understand the procedures. For the purpose of demonstration, I have used the following dataset as a sample.

Sample Dataset for How to Remove Comma in Excel Using Formula


1. Using SUBSTITUTE Function to Remove Comma in Excel

In general, the SUBSTITUTE function replaces one or more text strings by matching another text string. At first, it was used in Excel 2007. I will show you how to remove comma in Excel by using the SUBSTITUTE function. For example, consider the dataset below.

Sample Dataset for How to Remove Comma in Excel Using Substitute Function

Steps:

  • Firstly, write down the following formula in the formula bar.

=SUBSTITUTE(B5,",","")

How to Remove Comma in Excel Using SUBSTITUTE Function

  • Secondly, press the Enter key and use the Autofill tool for the entire column.

  • Finally, you will get all the texts without commas as shown in the picture below.


2. Applying Excel NUMBERVALUE Function to Remove Comma

In the second approach, I will utilize the NUMBERVALUE function to remove comma in Excel. You can use this approach when you need to remove commas from numbers. Hence, consider the image below as a sample dataset and follow the steps below to easily complete the operation.

Sample Dataset for How to Remove Comma in Excel Using NUMBERVALUE Function

Steps:

  • At first, put the following formula into the formula bar.

=NUMBERVALUE(C5)

How to Remove Comma in Excel Utilizing NUMBERVALUE Function

  • Then, hit the Enter Again, apply the Autofill tool in order to get a similar result for the complete dataset.

  • Lastly, You will find your desired output in the following image.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


Conclusion

These are all the steps you can follow in Excel to remove comma in Excel using formula. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.


Related Articles

<< Go Back To Remove Comma in Excel | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo