formula auditing/evaluate formula
 

I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

formula auditing/evaluate formula
 

Why would you, that's why min is wrapped around that part


--


Regards,


Peo Sjoblom



"F. Lawrence Kulchar" wrote in
message ...
I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

formula auditing/evaluate formula
 

When I perform 'formula auditing'/evaluate formula...I get:
for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345678 9")
the following:
({4,6,7,8,2,10,11,12,13})
Why do I NOT SIMPLY GET: 4

Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9}

So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13}

--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote in
message ...
I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

formula auditing/evaluate formula
 

On Sat, 26 Jul 2008 19:10:01 -0700, F. Lawrence Kulchar
wrote:

I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345678 9")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

I suspect you have a typo and that you really get back:

{4,5,6,7,8,2,10,11,12,13}

The find_text argument within the SEARCH function is an array, so SEARCH
returns an array showing the locations of all of the find_text arguments.

Your within_text string will be A7&"0123456789" or "A550123456789"

The first find_text argument is "0" which is in position four.
The next find_text argument is "1" which is in position five.
And so forth.

It is one of life's mysteries as to why some Excel functions return arrays and
others do not.

Some are documented to do so and some are not.

--ron

formula auditing/evaluate formula
 

I fully understand; thank you very much!

FLKulchar

"Ron Rosenfeld" wrote in message
...
On Sat, 26 Jul 2008 19:10:01 -0700, F. Lawrence Kulchar
wrote:

I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"01234567 89")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

I suspect you have a typo and that you really get back:

{4,5,6,7,8,2,10,11,12,13}

The find_text argument within the SEARCH function is an array, so SEARCH
returns an array showing the locations of all of the find_text arguments.

Your within_text string will be A7&"0123456789" or "A550123456789"

The first find_text argument is "0" which is in position four.
The next find_text argument is "1" which is in position five.
And so forth.

It is one of life's mysteries as to why some Excel functions return arrays
and
others do not.

Some are documented to do so and some are not.

--ron

formula auditing/evaluate formula
 

I fully understand; thank you very much!

FLKulchar
"T. Valko" wrote in message
...
When I perform 'formula auditing'/evaluate formula...I get:
for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"01234567 89")
the following:
({4,6,7,8,2,10,11,12,13})
Why do I NOT SIMPLY GET: 4

Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9}

So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13}

--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote
in message ...
I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar

formula auditing/evaluate formula
 

You're welcome!

--
Biff
Microsoft Excel MVP


"FLKulchar" wrote in message
...
I fully understand; thank you very much!

FLKulchar
"T. Valko" wrote in message
...
When I perform 'formula auditing'/evaluate formula...I get:
for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456 789")
the following:
({4,6,7,8,2,10,11,12,13})
Why do I NOT SIMPLY GET: 4

Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9}

So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13}

--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote
in message ...
I have:

In Cell A7: A55

In Cell B7:

=LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7)))))

When I perform 'formula auditing'/evaluate formula...I get:

for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ")

the following:

({4,6,7,8,2,10,11,12,13})

Why do I NOT SIMPLY GET: 4

Thanks,

FLKulchar