☰

如何在单元格中的 Excel 文本值中查找和替换字符“*”，但不能替换公式

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function vba microsoft-excel-2013
如何在单元格中的 Excel 文本值中查找和替换字符“*”，但不能替换公式

与这个问题非常相似： 如何在 Excel 中查找和替换字符“*”

但我需要保留公式不变。我有大约 50 多张工作表，其中有两种类型的单元格带有“*”

案例 1 的内容 - 单元格的值可能是：“*** 1.43”

案例 2 的内容 - 单元格的值可能是：“=100*B3”

我想查找并将案例 1 中的所有星号替换为“”，同时忽略包含使用星号作为乘数的公式的案例 2 单元格。基本上，更改静态单元格，但不要更改带有公式的单元格。

谢谢！

答案1

如果您拥有 Excel 2013 或更高版本，则可以使用以下例程。将代码放在 ThisWorkbook 模块中。运行一次；它将更新整个工作簿中的相应单元格。

Sub SetStarsBlank()

    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.UsedRange
            If Not Application.WorksheetFunction.IsFormula(c) Then
                c.Value = Replace(c.Value, "*", "")
            End If
        Next
    Next

End Sub

答案2

首先，复制一份您的文件，以防出现问题。

然后创建以下 VBA 子程序：

Sub StillLearning1()

    For Each c In Range("A1:Z100")
        If Not c.HasFormula Then
            temp = WorksheetFunction.Substitute(c.Value, "*", "")
            If Left(temp, 1) = "=" Or Left(temp, 1) = "'" Then
                temp = "'" & temp
            End If
            c.Value = temp
        End If
    Next c

End Sub

看如何在 MS Office 中添加 VBA？有关如何在 Excel 和其他 Microsoft Office 应用程序中使用 VBA 的一般信息。  StillLearning1只是一个任意的子例程名称；使用任何你想要的名字。更改A1:Z100为您希望替换发生的范围。我希望其余部分有点不言自明：

  • For Each c...Next c是一个循环，执行 2600 次（针对样本范围A1:Z100；26 列×100 行），并c转到该范围内的每个单元格。

  • c.HasFormula是一个布尔值，用于判断单元格是否c包含公式。这比检查第一个字符是否为 稍微可靠一些=，因为包含 的单元格'===是字符串（文本）值，但它看起来像===，因此它看起来会以 开头=。

    如果单元格包含公式，则不要管它。如果没有包含公式，进行替换。

  • 检查替换结果是否以=或开头'。如果是这样，则将其直接分配给单元格会导致 Excel 以不理想的方式解释第一个字符，因此请在前面添加 来保护它'。

然后运行子程序。

答案3

如果愿意，您可以使用基于公式的方法。

它取决于EVALUATE()在单元格公式中不起作用但在命名范围内起作用的函数。每个人都可以做到这一点，每个版本的 Excel。只需在单元格中键入“=EVALUATE(”，它看起来似乎不可用，但如果在命名范围内正确执行，它就会很好。

因此，创建两个命名范围，每个条件一个。将其中一个命名为“公式”，并在“引用”框中使用此公式创建一个命名范围：

=EVALUATE(FORMULATEXT(Sheet1!A1))

并创建另一个名为“NotFormula”的命名范围，在“引用”框中为其提供以下内容：

=EVALUATE(SUBSTITUTE(Sheet1!A1,"*",""))

（如果您选择要处理的单元格旁边的单元格，这里假设为 A1，那么在创建那些命名范围时将自己停在 B1 中，如果您注意到，这些命名范围使用相对引用。根据辅助列存在的位置选择停放位置。）

然后使用“辅助列”（要么就在电子表格中的输入处，要么在另一张表上，甚至可以为此添加一个然后删除。在其中输入以下公式：

=IF(ISFORMULA(A1),Formula,NotFormula)

如果它是一个公式，那么Formula结果就是它。如果不是，那么结果就是 `NotFormula。

Formula获取结果FORMULATEXT()并对其进行评估，将其给出的文本字符串转换为实际公式，至少在结果上如此，即使“技术上”并非如此。这是我所知道的唯一一种非宏实现方法。

NotFormula只需使用SUBSTITUTE()来替换星号。无需Find and Replace在星号前添加波浪号。请记住，此过程将在您的示例中生成一个字符串，因为“1.43”前面会有一个空格，但此方法将使其成为一个真正的数字。可能是您想要的，也可能不是。如果不是，请在命名范围内将函数包装在一个TEXT()函数中以保留该空间。

因此，必须有一个辅助列。这意味着，如果您不想将原始数据和结果并排显示，而是替换原始数据，则需要复制并粘贴值覆盖原始数据，然后删除辅助列。如果您确实希望将结果和原始数据并排显示，那么您已经很成功了。

但请记住，你做有EVALUATE()可用，但您不能在单元格中使用它。您必须在命名范围内使用它。

此外，27 年来他们一直在说它可能会消失。如果“27 年”这部分不能说服您它在未来是安全的，那么有一些迹象表明其他东西依赖于旧的 Excel 4 宏功能，这些功能的基础，而不是功能本身，并且 Excel 永远无法真正摆脱它们。所以......

相关内容