Extracting numbers from alphanumeric data

[Flu]

I have a long list of data which is alphanumeric. I would like to extract the
string last 3 digits number out.
Is there a quick forumula to it?

e.g.
SK109028-AMP3 - data extract should be 028
504467993AK3 - data extracted should be 993
505747995 - data extracted should be 995

 

[Bob Phillips]

=MID(A20,6,3)

 

[Rick Rothstein]

Give this formula a try...

=RIGHT(LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW($1:$99))),3)

where I have assumed your first data value is in A1 (this formula can be
copied down). Note that the formula returns text, not a number (so that
leading zeroes can be preserved as per your first example).

 

[Ron Rosenfeld]

I have a long list of data which is alphanumeric. I would like to extract the
string last 3 digits number out.
Is there a quick forumula to it?

e.g.
SK109028-AMP3 - data extract should be 028
504467993AK3 - data extracted should be 993
505747995 - data extracted should be 995

Another method that will give you a lot more functions and flexibility to use
in the future:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

Then use this formula (with your data in A1):

=REGEX.MID(A1,"\d{3}",-1)
--ron

 

[Flu]

thanks. This formular works wonders !!!

Give this formula a try...

=RIGHT(LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW($1:$99))),3)

where I have assumed your first data value is in A1 (this formula can be
copied down). Note that the formula returns text, not a number (so that
leading zeroes can be preserved as per your first example).