{"id":6988,"date":"2021-08-28T16:16:06","date_gmt":"2021-08-28T16:16:06","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=6988"},"modified":"2024-03-28T10:34:33","modified_gmt":"2024-03-28T10:34:33","slug":"remove-comma-in-excel","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/remove-comma-in-excel\/","title":{"rendered":"Remove Comma in Excel"},"content":{"rendered":"\n
Remove-Comma-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

In Excel, a lot of problems can occur due to the fact that we have not formatted something in the right way, or we need to replace or repair a large amount of data.<\/p>\n\n\n\n

For these issues, it is always useful to know some ways in which to operate and correct large data sets.<\/p>\n\n\n\n

One of the problems that we can stumble upon is having undesired commas in our files.<\/p>\n\n\n\n

Although commas are obligatory for some file formats (as in CSV- Comma Separated Values) we sometimes do not want commas to appear in our data.<\/p>\n\n\n\n

We will show different ways to remove commas in Excel, depending on the situation and the text that we are dealing with.<\/p>\n\n\n\n

Remove Comma with Formatting<\/h2>\n\n\n\n

For our example, we will use the table with loans, their amount, duration and date of approval, and banking officers that approved them.<\/p>\n\n\n\n

. <\/p>\n\n\n\n

In column B<\/strong>, we have loan amounts<\/a> in which a thousand values are separated with a comma. This is because this column is formatted as currency. To remove these commas, we have to change the formatting. We will do this by selecting the B column<\/strong>, and by going to Home >> Cells >> Format >> Format Cells<\/strong>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After that, we will just change the format, currently set as currencies, to General<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This way, we will remove the commas from our numbers, as seen below:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If we would want to create a number with commas or remove them for that matter, we can select column B<\/strong> and go to format again.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When we do it, we will select Number<\/strong> format and on the right side, we will notice that we have a button next to the \u201cUse 1000 separator (,)\u201d<\/strong> that we can select to get insert commas to separate a thousand in our number.<\/p>\n\n\n\n

Remove Comma with Find and Replace<\/h2>\n\n\n\n

We can use a simple Find & Replace<\/strong> option to replace all the commas in text with any sign that we want, in this case, with empty text.<\/p>\n\n\n\n

We will replace the commas in our column A<\/strong> with empty text by selecting column A<\/strong>, and then going to the Home tab >> Editing >> Find & Select >> Replace<\/strong>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We can also use a combination on a keyboard: CTRL + H<\/strong>.<\/p>\n\n\n\n

Both of these will get us to the same window:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In this window, we will put \u201c,\u201d<\/strong> as Find what<\/strong> sign, and empty text as Replace with<\/strong> option.<\/p>\n\n\n\n

We will click on Replace all <\/strong>and will lose commas in column A<\/strong> (names and last names).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you do not want to replace all the commas in your range, you can click on \u201cFind Next\u201d<\/strong> to go through every cell in the selection. In this case, all cells will be highlighted<\/a> one by one, so we can see which one we are changing.<\/p>\n\n\n\n

Remove Comma with Formula<\/h2>\n\n\n\n

We are going to revert the changes in column A<\/strong> to have the commas in it again. In a situation where we would like to keep the original column but have another one without commas, we can use the formula SUBSTITUTE<\/strong>.<\/p>\n\n\n\n

Our formula will be in column E<\/strong>, and it will go like this:<\/p>\n\n\n\n

=SUBSTITUTE(A2,\",\",\"\")<\/code><\/pre>\n\n\n\n
The SUBSTITUTE formula has three parameters:<\/p>\n\n\n\n