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

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

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

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

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