我如何只从Excel中获取数字 [英] How would I get only the numbers from excel

问题描述

我将如何从一个单元格中提取所有数字，例如A1单元格包含:"AA59"我希望公式提取59而忽略AA.我可以使用= if(right(A1)，2)公式，但是如果A1包含值A5，那么它将选择最后两个字符(即A5)，所以这是错误的吗?

How would I extract all the numbers from a cell, so for example A1 cell contains: "AA59" I would like the formula to extract 59 and ignore AA. I can use =if(right(A1),2) formula but if A1 contains value AA5 then it will select last two characters (which is A5), so this is wrong?

推荐答案

这是本机工作表公式解决方案.与VBA用户定义的函数相比，它有点不雅观，但它是标准的(非数组)公式，看起来比实际要复杂.

Here is a native worksheet formula solution. It's a little inelegant compared to a VBA user-defined-function, but is a standard (non-array) formula and looks more complex than it actually is.

B21中的公式是

=MID(A1, MIN(INDEX(ROW(INDIRECT("1:"&LEN(A1)))+((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)+(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>57))*1E+99,,)), 99)

根据需要填写.将整个内容包装在VALUE(...)函数中，或在MID之前加上双一进制(也称为 double minus 或-)，以将文本转换为真数字.

Fill down as necessary. Wrap the whole thing in a VALUE(...) function or precede MID with a double unary (aka double minus or --) to convert the text to a true number.

这篇关于我如何只从Excel中获取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！