Extracting 7 digit number from alphanumeric string
 

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500 ),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

Extracting 7 digit number from alphanumeric string
 

simpley add a LEN() function. Something like this

=if(len(my formula)=7,my formula,"")


=if(len(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW( $1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))))= 7,1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$50 0),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))),"")



"snowball" wrote:

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500 ),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

Extracting 7 digit number from alphanumeric string
 

Hi Joel,

Thanks for the reply. This formula works if my string only includes a 7
digit number e.g. abc1234567def will return 1234567 but if my string is
abc1234567def1 I get an error.

Is there a way of extracting a 7 digit number while omitting any other
numbers that may appear in the alphanumeric string?

All the rows in my data will contain this 7 digit number - and I need to
extract this number for all rows - however some rows will include other
numbers which I don't want.

Thanks,
snowball


"Joel" wrote:

simpley add a LEN() function. Something like this

=if(len(my formula)=7,my formula,"")


=if(len(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW( $1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))))= 7,1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$50 0),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))),"")



"snowball" wrote:

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500 ),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

Extracting 7 digit number from alphanumeric string
 

Hi,

Try this array formula

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"snowball" wrote:

Hi Joel,

Thanks for the reply. This formula works if my string only includes a 7
digit number e.g. abc1234567def will return 1234567 but if my string is
abc1234567def1 I get an error.

Is there a way of extracting a 7 digit number while omitting any other
numbers that may appear in the alphanumeric string?

All the rows in my data will contain this 7 digit number - and I need to
extract this number for all rows - however some rows will include other
numbers which I don't want.

Thanks,
snowball


"Joel" wrote:

simpley add a LEN() function. Something like this

=if(len(my formula)=7,my formula,"")


=if(len(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW( $1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))))= 7,1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$50 0),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))),"")



"snowball" wrote:

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500 ),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

Extracting 7 digit number from alphanumeric string
 

Thanks Mike,

This formula extracts the first group of digits in the string regardless of
the number of digits i.e. it doesn't search for the 7 digit number within the
string.

e.g. a123bcd765421ef will extract 123 when I would like it to extract
7654321. Is there any way to extract a 7 digit number and ignore any other
set of numbers (that are not 7 digits in a row)?

Thanks.


"Mike H" wrote:

Hi,

Try this array formula

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"snowball" wrote:

Hi Joel,

Thanks for the reply. This formula works if my string only includes a 7
digit number e.g. abc1234567def will return 1234567 but if my string is
abc1234567def1 I get an error.

Is there a way of extracting a 7 digit number while omitting any other
numbers that may appear in the alphanumeric string?

All the rows in my data will contain this 7 digit number - and I need to
extract this number for all rows - however some rows will include other
numbers which I don't want.

Thanks,
snowball


"Joel" wrote:

simpley add a LEN() function. Something like this

=if(len(my formula)=7,my formula,"")


=if(len(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW( $1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))))= 7,1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$50 0),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))),"")



"snowball" wrote:

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500 ),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

Extracting 7 digit number from alphanumeric string
 

On Mon, 31 Aug 2009 02:43:01 -0700, snowball
wrote:

Hi Ron, Thanks for this - it's worked really well and is exactly what I need.

Thanks to everyone else too who gave me Excel formulae - these worked for
most of my data but there were always a few exceptions which the Visual Basic
can capture.

You're welcome. And thanks for the feedback.
--ron