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

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.


Download Practice Workbook

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


2 Simple Approaches to Remove Comma in Excel

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.

Read More: How to Remove Inverted Comma in Excel (7 Easy Methods)


2. Utilizing 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.

Read More: How to Remove Comma Between Numbers in Excel


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.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo