How to Remove Comma in Excel? 5 Easy Ways

• Facebook
• Twitter
• YouTube

(Note: This guide on how to remove comma in Excel is suitable for all Excel versions including Office 365)

Commas are very common punctuation to separate text values. In numerical too, commas are used to separate unit places like hundreds or millions.

They help separate words or characters and act as visual cues to help the reader understand the context easily.

For example:

• Reading and understanding 415,816,157 is easier than reading 145816157, and
• Ascertaining January, February, March, April is easier than just having January February March April.

In Excel too, you can add commas in between the text or the numerals to act as visual cues to separate data. However, if you want to use any functions or formula, the presence of commas can be a hindrance.

In such cases, you might need to remove the commas in the cells.

In this article, I will tell you how to remove comma in Excel from both the numerals and text strings.

You’ll Learn:

• How to Remove Comma in Excel? 
  • From Numerical Values
    • By Changing the Formatting of the Text
    • Using the NUMBERVALUE Function
  • From Text Strings
    • Using Find and Replace
    • Using SUBSTITUTE Function
    • Using Text to Columns

Watch this Video on “How to Remove Comma in Excel?”

Related Reads:

How to Convert Excel to CSV File? 2 Different Approaches

How to Change Row Height in Excel? 5 Easy Ways

How to Split Cells Diagonally in Excel? 2 Easy Ways

How to Remove Comma in Excel?

From Numerical Values

There are a couple of methods used to remove commas from numerical values in Excel.

Let us see how with the help of an example.

Consider an Excel worksheet that contains data on the net value of 6 companies. Here, you can see that the value of each company is separated by a comma.

Though this data is easy to read, the presence of commas might cause some compatibility issues when using formulas and functions.

Let us see how to remove them.

By Changing the Formatting of the Text

When you type in any data, the format of the data defaults to General. If you enter any specific characters like a comma(,) or a dollar($) sign, the formatting of the cell gets changed automatically.

You can also change the format of the cells from the dropdown and make the commas disappear.

• To remove commas from the number, first, select the data with the cells.

• Navigate to Home. Under the Number section, click on the Number Format option which can be seen as a small extend arrow.

• You can also open the Format Cells dialog box by selecting the cells and right-clicking on the Format Cells option, or by pressing the keyboard shortcut key Ctrl + 1.

• This opens the Format Cells dialog box.

• In the Format Cells dialog box, click on the Number section. You can see the category of the cells is already selected as Number. If not, change the category to Number.

• Uncheck the checkbox for Use 1000 separator(,) and click OK.

• This changes the formatting of the cells and thereby removing any comma in between.

Note: When you see the comma between numbers in Excel, in most cases it will be a result of the cell formatting. You can note this by looking at the formula bar. If the number in the formula bar bears a comma, this is because of improper formatting. If the number appears with a comma in the formula bar, then the number might be formatted as text.

Using the NUMBERVALUE Function

There are certain functions you can use to remove commas from numerical values in Excel. Using the NUMBERVALUE formula, you can easily remove the commas from the cells instantly. As its name suggests, this function converts text to numbers.

This function mainly works in cases where the numbers are formatted as texts. However, this function needs an additional column or cell to convert the original value.

Consider the above-given example, where the numerical value is separated by commas.

• Choose a destination to convert the values.

• Enter the formula =NUMBERVALUE(number) in the destination cell. The number argument can be a constant value or a variable such as a cell number that corresponds to the cell.

• Press Enter.

• This converts the value in the cell with a comma into a numerical value without a comma.

• Now, you can use the drag handle to apply the formula to the other existing cells.

You can copy and paste the values into the original destination as Values.

Suggested Reads:

How to Save Excel Chart as Image? 4 Simple Ways

How to Create Excel Drop Down List With Color?

How to Save Excel as PDF? 5 Useful Ways

From Text Strings

We saw how to remove the comma from numerical values, let us now see how to remove the comma from text strings in Excel.

Removing commas in text strings is a bit different than removing commas from numerical values. Here, the commas are a part of the string, whereas in the numerical values they act as an additional notation.

Consider an example where the Excel worksheet contains the first name, last name, and age of the employees who’ll be participating in a program.

Let us now see different methods to remove commas from text strings in Excel with the above example.

Using Find and Replace

Using the Find and Replace function in Excel you can easily remove the commas. This method works on the principle that using the Find option, you can find the commas(,) in between text strings and replace them either with a blank or with a space.

However, you can only use the Find and Replace option to remove commas from text strings. This method does not work for numerical values.

• To remove the commas from the text values, first, select the cells with the text.

• Navigate to Home. Under the Editing section, click on the dropdown from Find & Select and select Replace.

• You can also use the keyboard shortcut Ctrl+H to open the Find and Replace dialog box.

• This opens the Find and Replace dialog box.

• Click on the Replace tab, if it is not selected.

• In the Find what textbox, enter a comma(,). Then, leave the Replace with dialog box empty or just add a space.

• Click on Replace All.

• This replaces all the commas from the text in the cells and Excel throws a popup showing the number of places replaced.

• Click OK.

• This method removes all the commas that appear in the selected cells and gives you the required values.

Note: Since this function replaces the new data in place of the original data, it is always better to make a copy of the data and then use this method.

Using SUBSTITUTE Function

This is yet another function you can use to remove commas from text strings in Excel. The working of this function is somewhat similar to the working of the Find and Replace method.

This method also finds the commas in the selected values and substitutes them with the character of your choice. This character can be a blank, space, or anything of your choice.

• To remove commas from the text, first, select any destination cell.

• Enter the formula =SUBSTITUTE(text,”,”,” “).

This function takes three arguments:

• The first argument(text) is the text string or cell name which houses the text string.

• The second argument given between the double quotations denotes the commas to be found.

• The third argument within the double quotations denotes the comma to be substituted. In this case, we have given a space.

• Press Enter.

• This instantly gives the output of the function. That is, the function returns the selected data without any commas.

• You can then use the drag handle to apply the formula to other cells.

Using Text to Columns

This is also an easy and efficient way to remove commas from a text in Excel. But when you use this method, the data is split into different columns instead of being separated in the same column.

• To remove the comma from the text, first, select the text.

• Navigate to Data. Under the Data Tools section, click on Text to Columns.

• This opens the Text to Columns wizard.

• In the first step, click on Delimited. And then click on Next.

• In the second step, choose the Delimiter. In this case, select Comma.

• You can see the preview of how the data would look in the Data Preview section.

• Click on Next.

• In the final step, choose the format of the data column. If you have no preference, just click on Finish.

• Once you click on Finish, you can see the data separated into different columns and the comma is removed.

Frequently Asked Questions

Also Read:

How to Find and Replace in Excel? A Step-by-Step Guide

How to Use the Excel TREND Function? A Step-by-Step Guide

How to Hide Formula Bar in Excel (and Show)? 3 Easy Ways

Closing Thoughts

There might be multiple reasons to remove the comma between text or numerical values in Excel. Too many commas in a single cell might make the data hard to read. Also, for using certain functions and formulas, there might be a need for you to remove the comma.

In this article, we saw how to remove comma in Excel in 5 easy ways. Depending on whether you’ll be removing the comma from the number or the text, you can choose the method that suits your purpose the best.

Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!