excel在文本加引用数字_如何在Excel中查找和替换文本和数字

excel在文本加引用数字

The Find and Replace tool is a powerful yet often forgotten feature of Excel. Let’s see how it can be used to find and replace text and numbers in a spreadsheet and also some of its advanced features.

查找和替换工具是Excel的功能强大但经常被遗忘的功能。 让我们看看如何将其用于查找和替换电子表格中的文本和数字，以及其某些高级功能。

在Excel中查找和替换文本和数字 (Find and Replace Text and Numbers in Excel)

When working with large spreadsheets, it is a common task to need to find a specific value. Fortunately, Find and Replace make this a simple task.

使用大型电子表格时，通常需要查找特定值。 幸运的是，“查找和替换”使此任务变得简单。

Select the column or range of cells you want to analyze or click any cell to search the entire worksheet. Click Home > Find & Select > Find or press the Ctrl+F keyboard shortcut.

选择要分析的列或单元格范围，或单击任何单元格以搜索整个工作表。 单击主页>查找并选择>查找，或按Ctrl + F键盘快捷键。

Type the text or number you want to search for in the “Find What” text box.

在“查找内容”文本框中键入要搜索的文本或数字。

Click “Find Next” to locate the first occurrence of the value in the search area; click “Find Next” again to find the second occurrence, and so on.

单击“查找下一个”以找到该值在搜索区域中的第一个匹配项； 再次单击“查找下一个”以查找第二个匹配项，依此类推。

Next, select “Find All” to list all occurrences of the value including information, such as the book, sheet, and cell where it is located. Click on the item in the list to be taken to that cell.

接下来，选择“全部查找”以列出所有出现的值，包括信息，例如书，表和它所在的单元格。 单击列表中的项目以将其带到该单元格。

Finding specific or all occurrences of a value in a spreadsheet is useful and can save hours of scrolling through.

查找电子表格中某个值的特定或所有出现是有用的，并且可以节省滚动时间。

If you want to change the occurrences of a value with something else, click the “Replace” tab. Type the text or number you want to use as a replacement value within the “Replace With” text box.

如果要用其他方法更改值的出现，请单击“替换”选项卡。 在“替换为”文本框中键入要用作替换值的文本或数字。

Click “Replace” to change each occurrence one at a time or click “Replace All” to change all occurrences of that value in the selected range.

单击“替换”一次更改一次每个出现，或单击“全部替换”更改所选范围内该值的所有出现。

探索高级选项 (Explore the Advanced Options)

Find and Replace has advanced features that many users are not aware of. Click the “Options” button to expand the window and see these.

查找和替换具有许多用户不知道的高级功能。 单击“选项”按钮以展开窗口并查看。

One really useful setting is the ability to change from looking within the active worksheet to the workbook.

一种真正有用的设置是能够从在活动工作表中浏览到工作簿。

Click the “Within” list arrow to change this to Workbook.

单击“内部”列表箭头，将其更改为“工作簿”。

Other useful options include the “Match Case” and “Match Entire Cell Contents” checkboxes.

其他有用的选项包括“匹配大小写”和“匹配整个单元格内容”复选框。

These options can help narrow down your search criteria, ensuring you find and replace the correct occurrences of the values you’re looking for.

这些选项可以帮助您缩小搜索条件的范围，确保您找到并替换要查找的值的正确位置。

更改值的格式 (Change the Formatting of Values)

You can also find and replace the formatting of values.

您还可以查找并替换值的格式。

Select the range of cells you want to find and replace in or click any cell to search the entire active worksheet.

选择要查找并替换的单元格范围，或单击任何单元格以搜索整个活动工作表。

Click Home > Find & Select > Replace to open the Find and Replace dialog box.

单击主页>查找和选择>替换以打开“查找和替换”对话框。

Select the “Options” button to expand the Find and Replace options.

选择“选项”按钮以展开“查找和替换”选项。

You do not need to enter text or numbers that you want to find and replace unless required.

除非需要，否则无需输入要查找和替换的文本或数字。

Click the “Format” button next to the “Find What” and “Replace With” text boxes to set the formatting.

单击“查找内容”和“替换为”文本框旁边的“格式”按钮以设置格式。

Specify the formatting you want to find or replace.

指定要查找或替换的格式。

A preview of the formatting is shown in the Find and Replace window.

格式的预览显示在“查找和替换”窗口中。

Continue with any other options you want to set and then click “Replace All” to change all occurrences of the formatting.

继续使用您要设置的任何其他选项，然后单击“全部替换”以更改所有出现的格式。

使用通配符 (Using Wildcard Characters)

When using Find and Replace, sometimes you might need to perform partial matches using wildcard characters.

使用查找和替换时，有时您可能需要使用通配符执行部分匹配。

There are two wildcard characters you can use in Find and Replace. The question mark and the asterisk. The question mark (?) is used to find a single character. For example, Al?n would find “Alan,” “Alen,” and “Alun.”

您可以在“查找和替换”中使用两个通配符。 问号和星号。 问号(？)用于查找单个字符。 例如，Al？n会找到“ Alan”，“ Alen”和“ Alun”。

The asterisk (*) replaces any number of characters. For example, y* would find “yes,” “yeah,” “yesss,” and “yay.”

星号(*)替换任意数量的字符。 例如，y *将找到“是”，“是”，“是”和“是”。

In this example, we have a list of names followed by an ID in column A of our spreadsheet. They follow this format: Alan Murray – 5367.

在此示例中，我们在电子表格的A列中有一个名称列表，后跟一个ID。 他们遵循以下格式：Alan Murray – 5367。

We want to replace all occurrences of the ID with nothing to remove them. This will leave us with just the names.

我们希望将所有出现的ID替换为没有要删除的ID。 这将只剩下名称。

Click Home > Find & Select > Replace to open the Find and Replace dialog box.

单击主页>查找和选择>替换以打开“查找和替换”对话框。

Type ” – *” in the “Find What” text box (there are spaces before and after the hyphen). Leave the “Replace With” text box empty.

在“查找内容”文本框中键入“ – *”(连字符前后都有空格)。 将“替换为”文本框留空。

Click “Replace All” to modify your spreadsheet.

单击“全部替换”以修改您的电子表格。

翻译自: https://www.howtogeek.com/442859/how-to-find-and-replace-text-and-numbers-in-excel/

excel在文本加引用数字