Extract the last three numbers from a string

[gloudy31]

ACH 275F536CH
APA 275F542
SHB 080B530G
AOA 050N535-ACP

Say I have the 4 lot numbers listed above in cells A1 thru A4. In cells B1 thru B4 I want to extract the last 3 numbers from each string accordingly so it would appear as shown below.

536
542
530
535

Is there a formula or set of formulas that can accomplish this?

Thanks so much for your help!

 

[Tetra201]

Try this formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=MID(A1,MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0))-2,3)

 

[Canapone]

Hi All,

in the example you shared could do the trick:

=MID(A1,9,3)+0


Another approach using AGGREGATE

=MID(A1,AGGREGATE(14,6,ROW($1:$100)/ISNUMBER(MID(A1,ROW($1:$100),1)+0),1)-2,3)+0


For sure they are less robust than Tetra201's solution.

 

[gloudy31]

Thanks guys. I really appreciate it!

 

[Sektor]

@Tetra201 Your formula doesn't work for "APA 275F542".

Here's UDF which correctly works with all four examples:

Function GetNumber(cell)
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Pattern = "(\d{3})-?[a-z]*$"
        With .Execute(cell)
            If .Count > 0 Then GetNumber = .Item(0).SubMatches(0) Else GetNumber = "Not Found"
        End With
    End With
End Function

 

[Tetra201]

@Tetra201 Your formula doesn't work for "APA 275F542".

It does. Double-checked on Excel 2010-32 and Excel 2013-32.

 

[Sektor]

I didn't notice, but two numbers don't work out.
Here's proof:

 

[Tetra201]

@Sektor:

Sorry, you are doing something wrong. Check if the formula is entered correctly.

 

[Sektor]