☰

从包含混合字母数字字符串的单元格中提取数字

tag-icon • tag-icon microsoft-excel
从包含混合字母数字字符串的单元格中提取数字

我有一列如下所示：

      A
1 om2222prakash
2 kumar83566
3 222gsrana
4 k4566hhhh23

如何才能找出 A1、A2、A3 和 A4 中的数字数据？我使用的是 Microsoft Excel 2007，希望输出如下所示：

      A
1 2222
2 83566
3 222
4 456623

答案1

ALT好吧，使用+打开 VBE（Visual Basic 编辑器）F11。在 VBA 项目资源管理器中右键单击并添加Module。

复制并粘贴以下代码，然后点击F5运行宏。

Option Explicit

Sub GetNumbers()

    Dim uColumn As String

    ' if your data is in a different column then change A to some other letter(s)
    uColumn = "A"

    Dim i As Long, j As Long, r As Range
    For i = 1 To Range(uColumn & Rows.Count).End(xlUp).Row
        Set r = Range(uColumn & i)
        Dim tmpStr As String
        tmpStr = vbNullString
        For j = 1 To Len(r)
            If IsNumeric(Right(Left(r, j), 1)) Then tmpStr = tmpStr & Right(Left(r, j), 1)
        Next j
        r.NumberFormat = "@"
        r = tmpStr
    Next i

End Sub

有一个名为uColumn。它A目前已指定为列。如果您的数据位于不同的列中，则将其更改A为您的列字母

希望这可以帮助

答案2

这是一个可行的公式。

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

ROW() 中的引用需要修改，以包含您想要考虑的所有数据行（在公式中引用两次）。例如，如果您的最后一行数据是 200，则它将读取 ROW($1:$2000)。

如果您的字母数字数据在，A1则将此公式放入B1并按 shift+enter（这是一个数组公式）。
这将仅从 A1 中获取数字并将其放在 B1 中。将 B1 的角向下拖到数据底部以自动填充其余数据。

您应该在 A 列中看到您的字母数字数据，而在 B 列中只看到该数据中的数字，这样您就可以对它们进行您想做的事情。

答案3

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AR81,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

使用替代字符替换字符串中的指定字符。示例中的数字被替换为空格，它应该可以与特殊字符等其他字符配合使用。

答案4

对于 2021 年之后搜索此问题的人：

我喜欢这种VALUE(MID())方法。简单又直接。

=VALUE(CONCAT(IFERROR(VALUE(MID(A1,ROW(1:32767),1)),"")))

=VALUE(CONCAT(IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),"")))

MID()将字符串中的字符收集成 1 个组。现在第二个版本，使用最SEQUENCE()适合的MID()起点，因为它只会根据需要生成尽可能多的 1 个字符长度的字符串，而不是它可能需要的全部 32,767 个，但由于单元格内容长度的限制，MID()第一个版本可以工作。ROW(1:32767)

VALUE()返回数字字符，其他内容则返回错误。请注意，由于数据显示仅关注正整数，因此这些公式不考虑小数或负值（尽管负值的“绝对值”将被保留）。

IFERROR()将错误更改为“空白”（""）。

CONCAT()将这些元素组合成一个字符串。比 简单TEXTJOIN()，但不可否认的是，简单程度并不高。

此时，如果期望结果是一个字符串，而不是正式的数字，则应停止。从可用性角度来看，Excel 实际上会在任何函数中将其作为数字处理，而使用字符串没有任何实际意义。这可能有用的一种方式是在查找中，如果查找键是所有字符串，因为查找的数字可能不起作用。但要将其视为数字，请将其全部用最后的 . 包裹起来VALUE()。

我不能说它在动态数组和随之而来的更改之前会起作用，因为我有更新版本，无法对此进行测试。这一点，以及它处理数组（范围）寻址的能力在 2013 年还不可用这一事实，意味着即使使用 SEQUENCE() 代替，CONCAT()它也不会起作用。ROW(1:32767)

但今天寻求此类问题解决方案的人们可能最好采用一种有效的公式，并且使用所谓的“最新和最好的”公式。

对于更复杂的字符串研究，MID()可以将字符收集到大于 1 个字符的组中。虽然可能很方便，但不如单个字符组方便。与此问题相关的一个示例可能是需要提取一个确切已知长度的数字（不必是数字，只要与此处的问题保持一致）（因为即使该长度对于每个单元格都不相同，也有某种方法可以知道确切的长度），因为可以形成该长度的组并通过VALUE()（或如果它不是所寻求的数字，则使用适用于该情况的其他测试）进行测试。当然，如果知道数字本身，那么简单的SUBSTITUTE()/LEN()测试是最好的，但如果只知道长度，那么这个VALUE(MID))想法就会起作用。

相关内容