从Excel 2010中的字母数字字符串中提取数字部分

本文介绍了从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))