formula auditing/evaluate formula

[F. Lawrence Kulchar]

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&"0123456789")),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

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[T. Valko]

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

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}

 

[Ron Rosenfeld]

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&"0123456789")),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

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

 

[FLKulchar]

I fully understand; thank you very much!

FLKulchar

 

[FLKulchar]

I fully understand; thank you very much!

FLKulchar

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I fully understand; thank you very much!

FLKulchar