How to remove commas from numeric and text values ​​in Excel

On numeric values, on text strings, using commas in the wrong places can really get annoying, even for the biggest Excel geeks. You may even know how to get rid of commas, but the method you know may be time-consuming for you. Well, no matter what your problem is, if it is related to a comma in the wrong place in your Excel worksheet, we can tell you one thing, all your problems will be solved today, right here!

Dive into this article to learn how to easily remove commas from numbers and text values ​​in the simplest steps possible. Hope you enjoy reading. Oh, and don’t forget to tell us which method catches your eye the most!

Section 1: How to remove commas from numerical values

When a numerical value contains a comma, there are two possible situations. One is that they actually contain commas, the other is that the commas are only visible due to formatting options. This section describes both scenarios.

Section 1.1: Changing Format Options by Changing

Click on one of the cells with a visible comma on the numeric value. Now, if you look at the text preview bar at the top, you'll see that you don't see the comma there. This means that the commas are only visible due to the formatting options and will disappear if we change the formatting.

Step 1: Right-click on the cell containing the comma and ## from the right-click context menu #Click "Set Cell Format " option.

Step 2: In the "Set CellFormat" window, click the "Number" at the top "Tab. Now under the Category option, click on the Number option.

On the

right side of the window, make sure that the checkbox corresponding to the Use 1000 delimiters (,) option is unchecked .

Finally click the

OK button.

Step 3: If you look at the Excel worksheet now, you will notice that the commas are no longer visible.

You can also apply the same format to other cells. To do this, just

click the cell you just formatted, then click the HOME tab at the top.

Next, click the

Format Painter button.

Step 4: Next, just drag and selectall the cells you want to apply the same formatting to. This will automatically remove commas from the selected cells.

Section 1.2: Using built-in formula functions

If you click on a cell that contains a comma, you can see the number even on the text preview bar of commas, which means the commas are actually present on the numbers. In this case, formatting the cells won't solve your problem. Instead, let's use the built-in function to remove commas from numeric values.

Step 1:Double-clickthe cell where you want to display the value without commas, then type or copy And paste the following formula.

=数值（A2）

Note: If the number containing the comma is in a different cell than in the A2 cell, remember to replace A1 for that specific cell id.

Step 2: That’s it. The function will now return the number without any commas. You can simply click and drag down the small square in the lower right corner of the cell containing the formula to apply the same formula to all cells below it.

Step 3: You can see that the formula is now applied to all cells below.

Section 2: How to Remove Commas from Text Values

Just like numeric values, text values ​​can also contain commas. There are several ways to eliminate commas in text strings. Let’s explore some of them in the sections below.

Section 2.1: Using the Find and Replace Method

This is a very simple method where we can eliminate the entire worksheet using the traditional Find and Replace functionality All commas present in .

Step 1: Press the CTRL F keys simultaneously to bring up the "Find and Replace" window.

Next, click on the

Replace tab.

Now in the

Find what field, enter comma.

将替换为字段留空。或者，如果您想用特定字符替换工作表中的逗号，而不是将此字段留空，您可以键入该字符。

最后，点击全部替换按钮以删除所有逗号。

第 2 步：您现在将能够看到一个对话框，说明已进行替换。点击确定按钮继续。

第 3 步：就是这样。如果您现在查看 Excel 工作表，您会发现所有的逗号都消失了。

第 2.2 节：通过使用替换函数替换所有逗号实例

在这种方法中，我们使用了一个名为Substitute的内置公式函数。Substitute 函数可以接受 4 个参数，其中第 4 个参数是可选的。第一个参数是源字符串，第二个参数是需要替换的字符，第三个参数是代替旧文本的新文本。最后，第 4 个参数是需要替换的字符的实例编号。

在本节中，我们将忽略第四个参数并将其留空。在 2.3 节中，我们将详细解释如何使用第4 个参数充分利用Substitute函数。

第 1 步：双击要显示逗号删除文本的单元格。现在将以下公式复制并粘贴到单元格上。

=SUBSTITUTE(A2,",","")

请记住将 A2 替换为包含源字符串的单元格的单元格 ID。第二个参数是“”，第三个参数是“”。

第 3 步：如果您按Enter键或单击其他地方，您可以看到逗号现在已成功删除。

与往常一样，您可以将公式向下拖动到下面的单元格中。为此，单击单元格角落的方形图标并将其向下拖动。

第 4 步：公式现在应用于所有单元格。享受！

第 2.3 节：通过使用替换函数替换逗号的特定实例

假设您的源字符串中有多个逗号，并且您只想替换其中一个。这就是 Substitue 函数的第四个参数出现的时候。Substitute函数的第 4 个参数接受要替换的字符的实例编号。只有这个实例会被替换。

第 1 步：在下面的示例中，我的源字符串有 2 个逗号，我只想用连字符替换最后一个。所以我的公式应该如下所示。

=SUBSTITUTE(A2,",","-",2)

注意：如果要替换第一个逗号而不是第二个逗号，则 Substitue 函数中的最后一个参数应该是 1 而不是 2。

第 2 步：按 Enter 键会立即为您提供结果，这是您想要的完美方式。

现在，像往常一样，单击方形图标并将其向下拖动以应用公式列。

第 3 步：现在公式在列范围内应用，您终于可以放松了。

第 2.4 节：使用快速填充

这是一种非常简单的方法，而且非常有效。通过flash fill法，你基本上可以教Excel你想让它学习的模式。让我们看看 flash fill 方法是如何在这里发挥作用的。

第 1 步：只需在结果列的第一行输入不带逗号的字符串。为了安全起见，请在一行中键入已删除逗号的字符串。

现在，单击第一行，然后同时按CTRL + E键。

第2步：魔术！快速填充学习模式并立即自动填充其余列。

The above is the detailed content of How to remove commas from numeric and text values ​​in Excel. For more information, please follow other related articles on the PHP Chinese website!