我想从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中的字母数字字符串中提取数字部分
发布评论