How would I get only the numbers from excel

问题:

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?

回答1:

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.

      

The formula in B21 is,

=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)

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.

回答2:

As an alternative, consider the Array Formula:

=--MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

to extract digits from a mixed cell:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

回答3:

If you are always trying to get a single or double digit number from the end of the string try this version:

=LOOKUP(100,RIGHT(A2,{1,2})+0)

回答4:

try this macro

Function RakamCikart(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RakamCikart = .Replace(Txt, "")
End With
End Function