Commas are used for multiple purposes when you work with spreadsheets. You can make data readable with the help of commas as they can separate the numbers, text, or other content. However, not always these commas are useful that’s why you often tend to remove them. Do you have any idea how to remove commas in Excel?
Removing commas is sometimes mandatory because a proper format of data is needed to get the right outcome. You can remove commas manually as well, but this could be tiring if the data is long enough.
Commas are used for:
- Understanding as well as reading 455, 677, 907 is easier as compared to 455677907.
- Also, January, February, March, April is easier than JanuaryFebruaryMArchApril.
Did you understand the importance of commas?
Let’s see how you can remove commas easily:
How to Remove Commas in Excel from Numerical Values?
From numerical values, you can use remove commas by using multiple approaches. Let’s consider an example:
- Suppose your dataset contains the net value of 6 companies and each company’s value is separated with a comma. When you apply formulas and functions, comma usage could be an issue.
Change the Formatting of the Text:
Whenever you enter data, the default format sets to the General option, whereas if your data contains characters such as a comma, or a dollar sign, the cell formatting will change by default. By changing the cell format from the drop-down menu, you can remove commas as well. Follow the steps given below:
- Choose the cells with data.
- Open the Home tab.
- Select the Number Format option given under the Number menu.
- Open the Format Cells dialog box and for this select the cells.
- Right-click on the Format Cells menu or else you can press the shortcut command CTRL + 1.
- Once the Format Cells dialog box appears choose the Number option.
- Click OK once you uncheck the checkbox for Use 1000 separator (,).
It simply changes the cell formatting and meanwhile the commas will get removed.
How to Remove Commas in Excel with Find and Replace?
Suppose your dataset has comma-separated values:
The Find and Replace feature of Excel will be used to remove commas from the cells. Not only this, you can even replace the commas with some character, space, or any other text. Follow the steps given below:
- Press CTRL + A from the keyboard to select the entire sheet to remove commas from the whole sheet. Or else you can select the All icon given at the upper left side of the sheet. If you don’t want to remove commas from the entire sheet, select a specific area of the sheet.
- Suppose you need to work on data in Column B.
- Open the Home tab and choose the Find and Select option from the Editing menu.
- Press the Replace button from the drop-down menu.
- The Find and Replace dialog box will pop up. Add a comma in the field box located next to the Find what option.
- You can replace the comma with space, if so, add a space in the input field next to Replace with. If you don’t want to replace anything with a comma, you can leave the field empty.
You can replace the commas one by one, for this click the Find Next option so that you can reach through every cell. Whenever a comma is highlighted, you can remove it or replace it with whatever you want.
How to Remove Commas with Formula?
Let’s assume you need to make some changes in column A of your dataset. These changes include getting back the commas in your dataset once again. Here you can use the SUBSTITUTE formula because one column will have the original data whereas the other column will have data without commas.
Put the formula in column E:
=SUBSTITUTE(A2,”,”,”)
Usually, the SUBSTITUTE formula includes three parameters:
Text– It is the range that helps in changing the text
Old_text– It helps to change the already existing text
New_text– It is the text you need to add rather than the original
This formula changes the commas in column A with the empty sign. Now, drag the formula down to the end cell. You will get the results instantly.
Summary:
In this post, you have learned how to remove commas in Excel by using multiple approaches. Excel always provides solutions to its users that’s why people love working with this awesome tool.
