从Excel 2010中的字母数字字符串中提取数字部分 [英] Extract numeric portion from an alphanumeric string in Excel 2010

问题描述

我想从excel中的字母数字字符串中提取所有数字.我有一个带有字母数字字符串列表的excel工作表，如下所示，我想从字母数字字符串中提取所有数字并将其存储在新单元格中

I would like to extract all the numbers from an alphanumeric string in excel. I have an excel sheet with list of alphanumeric strings as shown below and I would like to extract all the numbers from the alphanumeric string and store it in a new cell

我已经尝试过以下在线查找的公式，但结果输出为'6'，但这是不正确的，因此有人可以帮助我吗?

I already tried the below formula found online but it outputs '6' as result but it isn't right, so can anyone please help me with it?

SUM(MID(0&A2,LARGE(ISNUMBER(-- 
MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),
ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

我期望该字符串的输出:

I would expect the output of this string:

eed1e11bd1a66cb47ad8b215c882194cdf964332484d20c56aea69e6e5196f67

成为:

1111664782158821949643324842056696519667

请注意，我只希望通过Excel进行此操作.最好是一些功能而不是宏.

Please note that I wish to do this only via Excel. Preferrably some functions rather than macro.

推荐答案

这里是一个UDF，它将使用REGEX(通常是处理复杂的字符串操作的最快方法)来处理此问题.它删除所有不是数字的内容，并将其作为字符串返回.

Here's a UDF that will handle this using REGEX (normally the fastest way to handle complex string manipulations). It removes everything that isn't a number and returns it as a string.

Function NumbersOnly(rng As Range)
 Dim nReturn As Variant
 With CreateObject("VBScript.RegExp")
        .Pattern = "[^0-9]"
        .MultiLine = True
        .Global = True
        nReturn = .Replace(rng.Value2, vbNullString)
    End With
    NumbersOnly = nReturn
End Function

如果需要数字，只需将函数包装在"VALUE"函数中即可.

If you want a number simply wrap the function in a "VALUE" function.

=VALUE(NumbersOnly(A1))

这篇关于从Excel 2010中的字母数字字符串中提取数字部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！