Excel 如何删除文本并保留数字或者相反|极客笔记

如何在Microsoft Excel中删除文本并保留数字，或者相反

在本教程中，我们将学习如何通过使用原生公式和自定义函数来轻松地将文本与数字分开，在Microsoft Excel中分离文本与数字。我们还将学习如何将文本以及特定的数字轻松分割到两个单独的列中。

现在让我们假设：我们接收到原始数据，目的是分析和发现相应的数字与文本混合在一列中。而在各种情况下，将它们放在不同的列中以便仔细检查将更方便。

除了这些，如果我们处理的是同质数据，那么我们可以有效地使用LEFT、RIGHT和MID函数，从相同的位置提取相同数量的字符。

但这可能是实验室测试的理想情况，而在现实生活中，我们往往处理的是不同的数据，其中数字通常出现在所选文本之前、所选文本之后或文本之间：

现在下面的示例将为此情况提供有效的解决方案。

如何轻松删除给定数字中的文本？
如何在Excel表格中删除数字并保留文本？
如何在Microsoft Excel中将文本与数字分离到两个列中？
如何使用特殊工具在Microsoft Excel中删除数字或文本？

如何在Microsoft Excel中轻松删除给定数字中的文本

这个解决方案适用于Microsoft Excel 365、Microsoft Excel 2021和Microsoft Excel 2019。

我们都知道，Microsoft Excel 2019引入了一些以前版本中不可用的新功能，因此在这里我们使用了其中的一个函数，那就是：

TEXTJOIN

函数；此函数用于从包含数字的单元格中剥离文本字符。

可以在Microsoft Excel中使用的TEXTJOIN函数的通用公式如下：

TEXTJOIN (", TRUE, IFERROR (MID (cell, ROW (INDIRECT ("1:"&LEN (cell))), 1) *1, "))

此外，在Microsoft Excel 365和2021版本中，这个也将有效运行：

TEXTJOIN (", TRUE, IFERROR (MID (cell, SEQUENCE (LEN (cell)), 1) *1, "))

从第一眼看起，上述使用的公式可能显得有点吓人，但它们也非常有效：

例如： 如果我们想要从单元格A2中的数字中删除相应的文本，那么我们需要在单元格B2中输入以下其中一个公式，然后将其复制到我们需要的所有单元格中。

在Microsoft Excel 365 – 2019中，我们可以使用以下公式：

=TEXTJOIN (", TRUE, IFERROR (MID (A2, ROW (INDIRECT ( "1:"&LEN(A2))), 1) *1, "))

在 Microsoft Excel 2019 版本中，需要使用快捷键 Ctrl + Shift + Enter 将其输入为公式数组。在动态数组 Excel 中，这个公式将像正常公式一样工作，可以使用键盘上的回车键完成。

在 Microsoft Excel 365 和 2021 版本中，我们也可以使用下面提到的公式：

=TEXTJOIN (", TRUE, IFERROR (MID (A2, SEQUENCE (LEN (A2)), 1) *1, "))

作为结果, 单元格中的所有文本字符都将被移除，只保留数字：

如何在Microsoft Excel中删除文本并保留数字，或者相反

让我们现在看一下上述公式的工作原理：

为了更好地理解上述使用的逻辑，让我们从内部开始研究公式，以更好地理解它：

我们可以使用ROW(INDIRECT(“1:”&LEN(String)))或SEQUENCE(LEN(String))来创建与给定源字符串中字符总数相对应的数字序列，然后将这些顺序数字作为起始数字输入到MID函数中。就像在B2单元格的情况下，公式的这部分可能如下所示：

MID(A2, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}, 1)

在上述公式中， MID 函数的基本功能是从单元格A2中提取出每个字符，从第一个字符开始，并以数组的形式返回它们：

{"2";"1";"0";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}

之后，接收到的数组将乘以数字1。数字值在乘以一个非数字字符时将保持不变，而非数字字符将导致#VALUE!错误结果：

{2;1;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

而，各自的IFERROR函数将负责处理这些错误，并用空字符串替换它们：

{2;1;0;"";"";"";"";"";"";"";"";"";"";"";""}

现在，这个最终的数组被传递给 TEXTJOIN 函数，该函数将使用空字符串(“”)作为分隔符来连接给定数组中的非空值：

TEXTJOIN ("", TRUE, {2; 1; 0 ;"";"";"";"";"";"";"";"";"";"";"";""})

重要提示： 应该记住，某个解决方案适用于Microsoft Excel 2016-2007版本，但与其他解决方案相比，公式更加复杂。

如何在Microsoft Excel中使用自定义函数删除数字中的文本

这个解决方案在所有Excel版本中都能有效地工作。

假设我们使用的是旧版本的Microsoft Excel或者需要记住上述公式。在这种情况下，我们可以使用更简单的语法和用户友好的名称（如 RemoveText ）创建自己的函数。

除了这些之外，相应的用户定义函数（UDF）可以以两种较简单的方式编写，如下所示：

VBA（虚拟基本应用程序）代码1：

在这里，我们将逐个检查源字符串中的每个字符，然后检查它是否是数字。如果是数字，我们将有效地将该字符添加到结果字符串中：

Function RemoveText (str As String)
 Dim sRes As String 
        SRes = " "
 For i = 1 To Len (str) 
            If True = IsNumeric (Mid (str, i, 1)), Then 
            SRes = sRes & Mid (str, i, 1)
         End If
       Next i
 RemoveText = sRes 
End Function

VBA (Virtual Basic for Application)代码2：

代码将有效地创建一个对象以处理正则表达式。通过使用RegExp，我们将从源字符串中移除除0-9之外的所有数字字符：

Function RemoveText (str As String) As String 
With CreateObject ("VBScript.RegExp") 
.Global = True 
.Pattern = "[^0-9]" 
RemoveText =.Replace (str, "") End With
 End Function

在小型工作表上，这两种代码的表现都很好，而在调用该函数数百或数千次的大型工作表上，代码2使用了VBScript和RegExp，因为它们比其他方法更快速。

不论我们选择哪种方式，从最终用户的角度来看，用于删除所有文本然后保留数字的特定函数的目的很简单，就像这样：

RemoveText (String)

如果我们想要从单元格A2中删除非数字字符，那么在这种情况下，我们将使用单元格B2中的以下公式：

=RemoveText (A2)

现在之后我们将把它复制到列中，我们将得到这个结果：

如何在Microsoft Excel中删除文本并保留数字，或者相反

注意。可以明确指出，无论是原生的公式还是自定义函数的输出结果都是一个数字字符串。为了将其转换为数字，我们可以通过乘以1、加上0或将公式封装在VALUE函数中来实现。

例如：

=RemoveText (A2) + 0
=VALUE (RemoveText (A2))

如何从Microsoft Excel的文本字符串中删除数字

该解决方案适用于Microsoft Excel 365、Microsoft Excel 2021和Microsoft Excel 2019。

可以用于从字母数字字符串中删除数字的公式与前面例子中讨论的公式非常相似。

对于Microsoft Excel 365和2019版本：

TEXTJOIN("", TRUE, IF(ISERR(MID(cell, ROW(INDIRECT( "1:"&LEN(cell) )), 1) *1), MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1), ""))

在 Microsoft Excel 2019 版本中，我们只需按下 Ctrl + Shift + Enter 快捷键，就可以将其设置为数组公式。

而对于 Microsoft Excel 365 和 2021 版本，我们可以使用以下方法:

TEXTJOIN (", TRUE, IF (ISERROR (MID (cell, SEQUENCE (LEN (cell 1) *1), MID (cell, SEQUENCE (LEN (cell)), 1), "))

例如： 要从A2单元格中的字符串中去掉数字，公式如下：

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

或者

=TEXTJOIN (", TRUE, IF (ISERROR (MID (A2, SEQUENCE (LEN (A2)), 1) *1), MID (A2, SEQUENCE (LEN (A2)), 1), "))

结果是，在保留文本字符的同时，特定单元格中的所有数字都被移除。

如何在Microsoft Excel中删除文本并保留数字，或者相反

根据上面的截图，该公式将从给定字符串中剥离出任意位置的数字字符：无论是在开头、结尾还是中间。

为了去除文本前面的额外空格，我们将使用TRIM函数将该公式包裹起来，如下所示：

=TRIM(TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1), MID(A2, SEQUENCE(LEN(A2)), 1), "")))

如何在Microsoft Excel中删除文本并保留数字，或者相反

上述公式在Microsoft Excel中如何工作

假设这个特定的公式与前面的例子中解释的方式相同地工作。但唯一的区别是，我们需要从传递给TEXTJOIN函数的最终数组中删除数字而不是文本，并且为了完成这个操作，我们将使用IF和ISERROR函数的组合。

然后，ISERROR函数将捕获错误并将结果数组的布尔值传递给IF函数：

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

除此之外，当 IF 函数看到 TRUE（一个错误）时，它还会利用另一个 MID 函数将相应的文本字符插入到处理后的数组中。而如果 IF 函数遇到 FALSE（一个数字），则会将其替换为空字符串：

{"";"";"";" ";"S";"u";"n";"s";"e";"t";" ";"R";"o";"a";"d"}

这是最终的数组，传递给TEXTJOIN函数以连接相应的文本字符并输出结果。

如何在Excel中使用自定义函数删除文本中的数字

最好的一点是，这些特定解决方案对于所有的Excel版本都能有效地工作。

我们应该记住，我们将要使用的公式必须是强大且简单的，并且我们将分享用户定义函数（UDF）的代码，以便从所选的Excel工作表中剥离任何数字符号：

VBA代码1：

Function Removeumbers (str As String)
 Dim sRes As String

 sRes = "" 
For i = 1 To Len (str)
 If False = IsNumeric (Mid (str, i, 1)) 
Then sRes = sRes & Mid (str, i, 1)
 End If
 Next i 
RemoveNumbers = sRes 
End Function

VBA（Visual Basic for Applications）代码2：

Function RemoveNumbers (str As String) As String
 With CreateObject ("VBScript.RegExp") 
              .Global = True
              .Pattern = "[0-9]" 
              RemoveNumbers2 = .Replace (str, "") 
     End With 
End Function

现在就文本删除函数而言，第二段代码更适用于大型工作表，以实现性能的有效优化：

一旦将代码添加到工作簿中之后，就可以通过使用自定义函数轻松地从单元格中移除所有的数值字符：

RemoveNumbers (String)

就像我们的案例一样，单元格B2中的公式是：

=RemoveNumbers (A2)

为了去除前导空格（如果有的话），我们将自定义函数嵌套在TRIM函数中：

=TRIM(RemoveNumbers(A2))

如何在Microsoft Excel中删除文本并保留数字，或者相反

如何在Microsoft Excel中将数字和文本拆分到不同的列中

在特定情况下，当我们想要将文本和数字分开为两列时，我们将使用单个公式。为此，我们需要将 RemoveText 和 RemoveNumbers 函数的代码合并为一个名为 SplitTextNumbers 或 Split 的函数：

VBA（Visual Basic for Application）代码1：

Function SplitTextNumbers (str As String, is_remove_text As Boolean) As String
 Dim sNum, sText, sChar As String
 SCurChar = sNum = sText = "" 
For i = 1 To Len (str)
 SCurChar = mid (str, i, 1) 
If True = IsNumeric (sCurChar) Then 
SNum = sNum & sCurChar 
Else 
SText = sText & sCurChar 
End If 
Next i
 If True = is_remove_text Then 
SplitTextNumbers = sNum Else SplitTextNumbers = sText 
End If
 End Function

VBA（Visual Basic for Applications）代码2：

Function SplitTextNumbers (str As String, is_remove_text As Boolean) As String 
With CreateObject ("VBScript.RegExp") 
.Global = True 
If True = is_remove_text Then 
.Pattern = "[^0-9]" 
Else 
.Pattern = "[0-9]" 
End If
 SplitTextNumbers =.Replace (str, " ") 
End With End Function

而我们的新自定义函数需要两个参数：

SplitTextNumbers (string, is_remove_text)

is_remove_text是一个布尔值，表示需要删除哪些字符：

TRUE 或者 1：删除文本，保留数字。
FALSE 或者 0：减去数字，保留文本。

对于我们的示例数据集，公式的形式也会如下所示：

为了删除非数字字符：

=SplitTextNumbers (A2, TRUE)

为了删除数字字符：

=SplitTextNumbers (A2, FALSE)

如何在Microsoft Excel中删除文本并保留数字，或者相反

重要提示： 为了避免前导空格可能导致的问题，我们始终建议将公式包装起来，这样也会删除TRIM函数中的数字：

=TRIM (SplitTextNumbers (A2, FALSE))

如何利用特殊工具从Excel中移除数字或文本

对于那些不喜欢不必要复杂化事情的人来说，我们将展示一种在Excel表格中移除文本和数字的方法。

首先，在“数据”选项卡中，找到“文本”组，在“删除”下点击“删除字符”。
然后，在插件窗格中，选择源范围，并选择“删除字符集”选项，从下拉列表中选择文本字符或数字字符。
最后，点击“删除”，我们会得到相应的输出。

如何在Microsoft Excel中删除文本并保留数字，或者相反

Excel 如何删除文本并保留数字或者相反

如何在Microsoft Excel中删除文本并保留数字，或者相反

如何在Microsoft Excel中轻松删除给定数字中的文本

如何在Microsoft Excel中使用自定义函数删除数字中的文本

如何从Microsoft Excel的文本字符串中删除数字

上述公式在Microsoft Excel中如何工作

如何在Excel中使用自定义函数删除文本中的数字

如何在Microsoft Excel中将数字和文本拆分到不同的列中

如何利用特殊工具从Excel中移除数字或文本

Camera课程

Python教程

Java教程

Web教程

数据库教程

图形图像教程

办公软件教程

Linux教程

计算机教程

大数据教程

开发工具教程

Excel 精选教程

回顶部