Extracting numbers from alphanumeric data
 

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

Extracting numbers from alphanumeric data
 

=MID(A20,6,3)

--
__________________________________
HTH

Bob

"Flu" wrote in message
...
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

Extracting numbers from alphanumeric data
 

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&"012 3456789")),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).

--
Rick (MVP - Excel)


"Flu" wrote in message
...
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

Extracting numbers from alphanumeric data
 

On Wed, 3 Sep 2008 01:22:09 -0700, Flu wrote:

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 futu

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

Extracting numbers from alphanumeric data
 

thanks. This formular works wonders !!!

"Rick Rothstein" wrote:

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&"012 3456789")),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).

--
Rick (MVP - Excel)


"Flu" wrote in message
...
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