formula auditing/evaluate formula

  • Thread starter F. Lawrence Kulchar
  • Start date
F

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
 
P

Peo Sjoblom

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


--


Regards,


Peo Sjoblom
 
T

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
Click to expand...

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}
 
R

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Correction 5
Literal quotation marks retained from a formula 3
To extract the number if there is niumeric in right most digits 4
macro to copy following cells in the same column 4
Want to Use an Array; Not Sure of Syntax 4
How do i ignore letters in a formula? 2
Sheet name determined by cell value in INDEX/MATCH 2
Removing characters from a cell (keeping only numbers) 2 6

Top