With text, a comma can be used as a separator (such as a separator between the first and last name or address) or as a part of regular grammar.
And with numbers, it often used to make the numbers more readable (a thousand separators is the most common use of comma with numbers).
While comma does help make the data a lot more readable, sometimes, you may want to remove all the commas from the data (or remove specific instances of the comma).
In this tutorial, I will show you some simple ways to remove comma in Excel from text and numbers.
Working with data in Excel often means dealing with numbers that contain commas. While commas can help make large numbers more readable, they can cause errors in formulas and calculations. Thankfully, removing commas in Excel is easy to do with just a few clicks.
Why Remove Commas in Excel?
Here are some of the main reasons you may need to delete commas from Excel cells:
-
Formulas Commas break up numbers into separate text values, which causes errors in formulas For calculations to work properly, numeric data needs to be in a continuous number format without punctuation
-
Plots and Charts Excel charts and graphs won’t correctly plot data points containing commas, Removing them enables proper data visualization,
-
Data Imports: When importing numeric data from other sources, it may contain commas in values. Getting rid of commas enables seamless data imports.
-
Filtering and Sorting: Columns containing numbers with commas can’t be correctly filtered or sorted. Removing commas allows proper organization of numeric data.
-
VLOOKUP Errors: The VLOOKUP function looks for exact matches and returns errors if values contain commas and don’t precisely match.
How to Remove Commas with Find and Replace
The easiest way to delete commas in Excel is with the Find and Replace tool:
-
Select the cells containing commas you want to remove. You can select an entire column, non-adjacent rows and columns, or any other range of cells.
-
Press Ctrl + H to open the Find and Replace dialog box. Alternatively, go to the Home tab and click the Find & Select drop-down menu, then choose Replace.
-
In the Find what box, enter a comma surrounded by quotation marks (“,”), which will search for literal comma characters.
-
Leave the Replace with box empty.
-
Click the Replace All button to remove every comma in the selected cells.
And that’s it! This will instantly get rid of any commas in the highlighted range. The numbers will retain their values and be converted into a continuous number format.
Additional Find and Replace Tips
-
To undo replacements, use Ctrl + Z or the Undo button.
-
Make sure the “Match entire cell contents” box is unchecked so it finds commas anywhere in cells.
-
The Find and Replace tool can remove any character or text – just type it into the Find what box.
-
Use wildcards like asterisks (*) to find special characters or partial text strings.
-
Look at the number of replacements listed at the bottom to confirm the right amount of commas were removed.
Alternative Methods to Delete Commas
In addition to Find and Replace, you can use these options to eliminate commas in Excel:
-
Text to Columns – This Wizard can be used to split text and delimiters into separate columns. Select comma delimited data, then deselect comma and set columns to a single column to get a comma-free value.
-
Convert to Number – If data contains only numeric values with commas, converting it to an Excel number format will automatically remove commas. Select the cells, go to the Data tab, and click the Convert to Number option.
-
TRIM Function – The TRIM function removes extra spaces and non-printing characters but leaves only commas if no other characters are present. So combining it with SUBSTITUTE to replace commas with blank spaces removes them.
-
Manual Editing – You can manually delete individual commas by going cell-by-cell and deleting them. Useful for small data sets but time consuming for large ones.
Data Formatting to Prevent Commas
To avoid having to remove commas repeatedly, there are ways to format data so it doesn’t include commas in the first place:
-
Custom Number Formatting – In the Format Cells window, you can set a custom number format without built-in comma separators. For example:
#0or0. -
Paste Special – When pasting from external sources, use Paste Special to paste only the values into Excel, leaving formatting behind.
-
Import Options – Some data import wizards give you options to format numbers without commas during the import process.
-
Input Validation – Set a whole number only validation rule on cells to prevent entry of commas and invalid data.
Commas in Excel Reports and Exports
Note that removing commas from values in Excel does not delete them from reports or exports to Word/PDF. Additional steps are required:
-
For reports, modify the number format codes to exclude commas.
-
For exports, indicate comma removal in file export settings.
-
Alternatively, run Find/Replace on docs after exporting.
By mastering a few simple Excel skills, deleting unnecessary commas becomes a breeze. Next time you need to remove commas for calculations, visualizations, or data imports, use one of these quick methods to clean your spreadsheet columns. Proper data formatting will also help avoid commas creeping back in later.
Using the NUMBERVALUE Function
In case you have a situation where numbers are not actually numbers (but text) and you want to remove the comma from these numbers, you can use the NumberValue function.
This function does exactly this – converts text to a number.
Suppose you have a dataset as shown below where someone has manually entered the comma (or it came as a part of a data download).
You can see that these are not numbers as they align to the left in the cell (by default). Also, when you select a cell and see in the formula bar, you can see the commas as a part of the number (indicating that this is not numeric but text).
Below is the formula that will convert these text into numbers.
Copy this formula for all the cells in the column and you will have the numbers.
There is a possibility that you may still see the comma in the result of the formula, but that would then be because of number formatting. You can use the previous method to remove these commas from the numbers.
Remove Comma from Numbers
When working with numbers in Excel, you can apply various formats to the cells and the numbers would be displayed accordingly.
Since these commas are part of the formating and not the number itself, we need to edit the formatting to remove these commas from the numbers.
Suppose you have a dataset as shown below and you want to get rid of the commas from the numbers in column B.
Below are the steps to do this:
- Select the dataset (the one with the numbers)
- Click the Home tab in the ribbon
- In the number group, click on the dialogue box launcher (the small slanted arrow icon at the bottom-right part of the group)
- In the Format Cells dialogue box, make sure the Number tab is selected
- In the Category list, select Number (if not selected already)
- Uncheck the ‘Use 1000 Separator (,)’ option and specify how many decimal digits you want
- Click OK
The above steps would remove the comma from all the selected numbers.
Tip: You can also use the keyboard shortcut Control + 1 to open the format cells dialog box (after selecting the cells). Hold the Control key and then press the 1 key.
Remember that this method would only work where the comma was a part of the formatting and not the cell. To check this, select the cell and see the value in the formula bar. If you see commas in the formula bar, then these are not part of the formatting.
Remove Commas in Excel – 5 Ways!
How do I remove a comma from a column in Excel?
If you want to retain the value but remove the comma, you can use a formula in a new column. For example, if you have the value 1,234 in cell A1, you can enter the formula =SUBSTITUTE (A1,”,”,””) into cell B1, and it will remove the comma and keep the value as 1234.
How do I remove commas from text?
To use the Find and Replace feature to remove commas from texts: Select the cell range containing the texts with commas. Go to the Home tab > Editing group > Find & Select drop-down > Replace. You can also use the Ctrl + H keys to get the Find and Replace dialog box. The Find and Replace dialog box will appear.
How to replace commas in Excel?
If you want to replace the commas with space, then put a space in the input field next to ‘ Replace with ’. If you just want to remove the commas without replacing it with anything, then leave the field blank. Click the “ Replace All ” button to replace all the commas.
How do I remove commas from a dataset?
Type a comma into the “Find what:” text box. Leave the “Replace with:” field empty if you want to remove all of the commas in your dataset. If you want to replace the commas with another symbol, you can enter it in this box. Click on the “Replace All” button to remove the commas from your dataset.
