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.
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.
Steps:
- Firstly, write down the following formula in the formula bar.
=SUBSTITUTE(B5,",","")
- 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.
Steps:
- At first, put the following formula into the formula bar.
=NUMBERVALUE(C5)
- 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.