text search for a non-constant value?

Hi!

Can someone please explain how this monster works?

If the value of A1 is "somenum1or2", it really returens a "1".
I wonder why..

With A1="somenum1or2", "1:"&LEN(A1) yields 11.

"1:"&LEN(A1) yields "1:11".

However: indirect("1:11") yields 0. And Row(indirect("1:11"))
yields 1. And Row(indirect("88:11")) yields 11. It seems, that
Row(indirect("x:y")) for some number x and y just yields the
lower one. Strange.

Yes, this is maybe the wrong approach to understand this formula,
because it is an array formula. I know a little bit of those.
But not enough to understand this monster. I only have a vague
idea:

I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
an array of all charakters in A1 by calculating MID(A1,x,1) for
all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
values and the MATCH looks for the first TRUE value and returns its
index in the array. Finally, the MID returns just that char. Easy?!?

What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
an array of characters of the string in A1?

And what the hell does "--" mean / do?

Please enlight me...



KL wrote:
> Hi,
>
> Try this:
>
> =--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
>
> Regards,
> KL
>
>
> "xirx" <xirx@gmx.de> wrote in message
> news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
>
>>Hi!
>>
>>The seach/3 function searches for 'find_text' in
>>'within_text', starting at the 'start_nun's character:
>>
>>SEARCH(find_text,within_text,start_num)
>>
>>I need to find the first digit in a string. I guess,
>>search/3 does neither support regular expresseions,
>>no does it allow a function instead of a constant
>>'search_text'.
>>
>>One way to find the first digit is this monster:
>>
>>=MIN(
>> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
>> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
>> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
>> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
>> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
>> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
>> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
>> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
>> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
>> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
>>)
>>
>>Any more efficient way to do a
>>
>>search('[0-9]';D2) or search(isnumber();D2)
>>
>>?
>
>
>

type
=row(indirect("1:11"))
in a cell
then hit F2 followed by F9

You'll see that it does return an array.


=mid() returns a text string. If that string is numeric, then -- converts it to
a number. The first negative converts it to a negative number and the second
changes the sign to non-negative.

xirx wrote:
>
> Hi!
>
> Can someone please explain how this monster works?
>
> If the value of A1 is "somenum1or2", it really returens a "1".
> I wonder why..
>
> With A1="somenum1or2", "1:"&LEN(A1) yields 11.
>
> "1:"&LEN(A1) yields "1:11".
>
> However: indirect("1:11") yields 0. And Row(indirect("1:11"))
> yields 1. And Row(indirect("88:11")) yields 11. It seems, that
> Row(indirect("x:y")) for some number x and y just yields the
> lower one. Strange.
>
> Yes, this is maybe the wrong approach to understand this formula,
> because it is an array formula. I know a little bit of those.
> But not enough to understand this monster. I only have a vague
> idea:
>
> I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
> an array of all charakters in A1 by calculating MID(A1,x,1) for
> all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
> values and the MATCH looks for the first TRUE value and returns its
> index in the array. Finally, the MID returns just that char. Easy?!?
>
> What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
> an array of characters of the string in A1?
>
> And what the hell does "--" mean / do?
>
> Please enlight me...
>
> KL wrote:
> > Hi,
> >
> > Try this:
> >
> > =--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
> >
> > Regards,
> > KL
> >
> >
> > "xirx" <xirx@gmx.de> wrote in message
> > news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
> >
> >>Hi!
> >>
> >>The seach/3 function searches for 'find_text' in
> >>'within_text', starting at the 'start_nun's character:
> >>
> >>SEARCH(find_text,within_text,start_num)
> >>
> >>I need to find the first digit in a string. I guess,
> >>search/3 does neither support regular expresseions,
> >>no does it allow a function instead of a constant
> >>'search_text'.
> >>
> >>One way to find the first digit is this monster:
> >>
> >>=MIN(
> >> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
> >> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
> >> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
> >> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
> >> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
> >> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
> >> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
> >> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
> >> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
> >> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
> >>)
> >>
> >>Any more efficient way to do a
> >>
> >>search('[0-9]';D2) or search(isnumber();D2)
> >>
> >>?
> >
> >
> >

--

Dave Peterson

Dave,

I never doubt that row(indirect("1:11") does what it does...
however, I am not able to find that behaviour of the
row function in the online help. Here, it says "row(reference)
Returns the row number of a reference". And indirect(ref_text)
returns the reference specified by a text string. But "1:11"
does not look like a reference. - I still don't understand...

Regarding the --: You are telling me that --(A1) is identical
to (-1)*(-1)*A1?



Dave Peterson wrote:
> type
> =row(indirect("1:11"))
> in a cell
> then hit F2 followed by F9
>
> You'll see that it does return an array.
>
>
> =mid() returns a text string. If that string is numeric, then -- converts it to
> a number. The first negative converts it to a negative number and the second
> changes the sign to non-negative.
>
> xirx wrote:
>
>>Hi!
>>
>>Can someone please explain how this monster works?
>>
>>If the value of A1 is "somenum1or2", it really returens a "1".
>>I wonder why..
>>
>>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
>>
>>"1:"&LEN(A1) yields "1:11".
>>
>>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
>>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
>>Row(indirect("x:y")) for some number x and y just yields the
>>lower one. Strange.
>>
>>Yes, this is maybe the wrong approach to understand this formula,
>>because it is an array formula. I know a little bit of those.
>>But not enough to understand this monster. I only have a vague
>>idea:
>>
>>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
>>an array of all charakters in A1 by calculating MID(A1,x,1) for
>>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
>>values and the MATCH looks for the first TRUE value and returns its
>>index in the array. Finally, the MID returns just that char. Easy?!?
>>
>>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
>>an array of characters of the string in A1?
>>
>>And what the hell does "--" mean / do?
>>
>>Please enlight me...
>>
>>KL wrote:
>>
>>>Hi,
>>>
>>>Try this:
>>>
>>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
>>>
>>>Regards,
>>>KL
>>>
>>>
>>>"xirx" <xirx@gmx.de> wrote in message
>>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
>>>
>>>
>>>>Hi!
>>>>
>>>>The seach/3 function searches for 'find_text' in
>>>>'within_text', starting at the 'start_nun's character:
>>>>
>>>>SEARCH(find_text,within_text,start_num)
>>>>
>>>>I need to find the first digit in a string. I guess,
>>>>search/3 does neither support regular expresseions,
>>>>no does it allow a function instead of a constant
>>>>'search_text'.
>>>>
>>>>One way to find the first digit is this monster:
>>>>
>>>>=MIN(
>>>> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
>>>> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
>>>> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
>>>> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
>>>> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
>>>> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
>>>> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
>>>> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
>>>> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
>>>> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
>>>>)
>>>>
>>>>Any more efficient way to do a
>>>>
>>>>search('[0-9]';D2) or search(isnumber();D2)
>>>>
>>>>?
>>>
>>>
>>>
>

I bet you've seen =sum(a:a) or =sum(a:c).

=sum(1:11) would sum rows 1 to 11.

=indirect(a10) will look at the address in A10 and return the value in that
cell.

=indirect("A10") (as a string) will always point to A10--no matter if you
insert/delete rows or columns.

=indirect("1:11") has that same effect. If you delete rows 1:11, this part of
the formula won't break.

And yep. --A1 is the same as (-1)*(-1)*A1.

(as long as A1 looks like a number.)

xirx wrote:
>
> Dave,
>
> I never doubt that row(indirect("1:11") does what it does...
> however, I am not able to find that behaviour of the
> row function in the online help. Here, it says "row(reference)
> Returns the row number of a reference". And indirect(ref_text)
> returns the reference specified by a text string. But "1:11"
> does not look like a reference. - I still don't understand...
>
> Regarding the --: You are telling me that --(A1) is identical
> to (-1)*(-1)*A1?
>
> Dave Peterson wrote:
> > type
> > =row(indirect("1:11"))
> > in a cell
> > then hit F2 followed by F9
> >
> > You'll see that it does return an array.
> >
> >
> > =mid() returns a text string. If that string is numeric, then -- converts it to
> > a number. The first negative converts it to a negative number and the second
> > changes the sign to non-negative.
> >
> > xirx wrote:
> >
> >>Hi!
> >>
> >>Can someone please explain how this monster works?
> >>
> >>If the value of A1 is "somenum1or2", it really returens a "1".
> >>I wonder why..
> >>
> >>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
> >>
> >>"1:"&LEN(A1) yields "1:11".
> >>
> >>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
> >>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
> >>Row(indirect("x:y")) for some number x and y just yields the
> >>lower one. Strange.
> >>
> >>Yes, this is maybe the wrong approach to understand this formula,
> >>because it is an array formula. I know a little bit of those.
> >>But not enough to understand this monster. I only have a vague
> >>idea:
> >>
> >>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
> >>an array of all charakters in A1 by calculating MID(A1,x,1) for
> >>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
> >>values and the MATCH looks for the first TRUE value and returns its
> >>index in the array. Finally, the MID returns just that char. Easy?!?
> >>
> >>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
> >>an array of characters of the string in A1?
> >>
> >>And what the hell does "--" mean / do?
> >>
> >>Please enlight me...
> >>
> >>KL wrote:
> >>
> >>>Hi,
> >>>
> >>>Try this:
> >>>
> >>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
> >>>
> >>>Regards,
> >>>KL
> >>>
> >>>
> >>>"xirx" <xirx@gmx.de> wrote in message
> >>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
> >>>
> >>>
> >>>>Hi!
> >>>>
> >>>>The seach/3 function searches for 'find_text' in
> >>>>'within_text', starting at the 'start_nun's character:
> >>>>
> >>>>SEARCH(find_text,within_text,start_num)
> >>>>
> >>>>I need to find the first digit in a string. I guess,
> >>>>search/3 does neither support regular expresseions,
> >>>>no does it allow a function instead of a constant
> >>>>'search_text'.
> >>>>
> >>>>One way to find the first digit is this monster:
> >>>>
> >>>>=MIN(
> >>>> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
> >>>>)
> >>>>
> >>>>Any more efficient way to do a
> >>>>
> >>>>search('[0-9]';D2) or search(isnumber();D2)
> >>>>
> >>>>?
> >>>
> >>>
> >>>
> >

--

Dave Peterson

Ok. So, 1:11 just denotes rows 1 to 11. And "1:11" is a
string constantthat is not touched when rows are inserted
or deleted. The indirect function turns the string constant
"1:11" into a reference.

But according to Excels online help, the row function just
returns the row number of a reference. E.g., row(C10)=10.
And row(1:11)=1.

Why does row(1:11) return {1; 2; ...; 11} when I press F2
and F9?

Is 1:3 just some kind of shortcut for {1;2;3}?


Dave Peterson wrote:
> I bet you've seen =sum(a:a) or =sum(a:c).
>
> =sum(1:11) would sum rows 1 to 11.
>
> =indirect(a10) will look at the address in A10 and return the value in that
> cell.
>
> =indirect("A10") (as a string) will always point to A10--no matter if you
> insert/delete rows or columns.
>
> =indirect("1:11") has that same effect. If you delete rows 1:11, this part of
> the formula won't break.
>
> And yep. --A1 is the same as (-1)*(-1)*A1.
>
> (as long as A1 looks like a number.)
>
> xirx wrote:
>
>>Dave,
>>
>>I never doubt that row(indirect("1:11") does what it does...
>>however, I am not able to find that behaviour of the
>>row function in the online help. Here, it says "row(reference)
>>Returns the row number of a reference". And indirect(ref_text)
>>returns the reference specified by a text string. But "1:11"
>>does not look like a reference. - I still don't understand...
>>
>>Regarding the --: You are telling me that --(A1) is identical
>>to (-1)*(-1)*A1?
>>
>>Dave Peterson wrote:
>>
>>>type
>>>=row(indirect("1:11"))
>>>in a cell
>>>then hit F2 followed by F9
>>>
>>>You'll see that it does return an array.
>>>
>>>
>>>=mid() returns a text string. If that string is numeric, then -- converts it to
>>>a number. The first negative converts it to a negative number and the second
>>>changes the sign to non-negative.
>>>
>>>xirx wrote:
>>>
>>>
>>>>Hi!
>>>>
>>>>Can someone please explain how this monster works?
>>>>
>>>>If the value of A1 is "somenum1or2", it really returens a "1".
>>>>I wonder why..
>>>>
>>>>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
>>>>
>>>>"1:"&LEN(A1) yields "1:11".
>>>>
>>>>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
>>>>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
>>>>Row(indirect("x:y")) for some number x and y just yields the
>>>>lower one. Strange.
>>>>
>>>>Yes, this is maybe the wrong approach to understand this formula,
>>>>because it is an array formula. I know a little bit of those.
>>>>But not enough to understand this monster. I only have a vague
>>>>idea:
>>>>
>>>>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
>>>>an array of all charakters in A1 by calculating MID(A1,x,1) for
>>>>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
>>>>values and the MATCH looks for the first TRUE value and returns its
>>>>index in the array. Finally, the MID returns just that char. Easy?!?
>>>>
>>>>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
>>>>an array of characters of the string in A1?
>>>>
>>>>And what the hell does "--" mean / do?
>>>>
>>>>Please enlight me...
>>>>
>>>>KL wrote:
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>Try this:
>>>>>
>>>>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
>>>>>
>>>>>Regards,
>>>>>KL
>>>>>
>>>>>
>>>>>"xirx" <xirx@gmx.de> wrote in message
>>>>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
>>>>>
>>>>>
>>>>>
>>>>>>Hi!
>>>>>>
>>>>>>The seach/3 function searches for 'find_text' in
>>>>>>'within_text', starting at the 'start_nun's character:
>>>>>>
>>>>>>SEARCH(find_text,within_text,start_num)
>>>>>>
>>>>>>I need to find the first digit in a string. I guess,
>>>>>>search/3 does neither support regular expresseions,
>>>>>>no does it allow a function instead of a constant
>>>>>>'search_text'.
>>>>>>
>>>>>>One way to find the first digit is this monster:
>>>>>>
>>>>>>=MIN(
>>>>>>IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
>>>>>>IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
>>>>>>)
>>>>>>
>>>>>>Any more efficient way to do a
>>>>>>
>>>>>>search('[0-9]';D2) or search(isnumber();D2)
>>>>>>
>>>>>>?
>>>>>
>>>>>
>>>>>
>

It's an array formula. That means excel will essentially loop through each
element for the formula.

Not too much different than:

=average(if(a1:a10="x",b1:b10))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you want to read more about array formulas, visit Chip Pearson's site:
http://cpearson.com/excel/array.htm




xirx wrote:
>
> Ok. So, 1:11 just denotes rows 1 to 11. And "1:11" is a
> string constantthat is not touched when rows are inserted
> or deleted. The indirect function turns the string constant
> "1:11" into a reference.
>
> But according to Excels online help, the row function just
> returns the row number of a reference. E.g., row(C10)=10.
> And row(1:11)=1.
>
> Why does row(1:11) return {1; 2; ...; 11} when I press F2
> and F9?
>
> Is 1:3 just some kind of shortcut for {1;2;3}?
>
> Dave Peterson wrote:
> > I bet you've seen =sum(a:a) or =sum(a:c).
> >
> > =sum(1:11) would sum rows 1 to 11.
> >
> > =indirect(a10) will look at the address in A10 and return the value in that
> > cell.
> >
> > =indirect("A10") (as a string) will always point to A10--no matter if you
> > insert/delete rows or columns.
> >
> > =indirect("1:11") has that same effect. If you delete rows 1:11, this part of
> > the formula won't break.
> >
> > And yep. --A1 is the same as (-1)*(-1)*A1.
> >
> > (as long as A1 looks like a number.)
> >
> > xirx wrote:
> >
> >>Dave,
> >>
> >>I never doubt that row(indirect("1:11") does what it does...
> >>however, I am not able to find that behaviour of the
> >>row function in the online help. Here, it says "row(reference)
> >>Returns the row number of a reference". And indirect(ref_text)
> >>returns the reference specified by a text string. But "1:11"
> >>does not look like a reference. - I still don't understand...
> >>
> >>Regarding the --: You are telling me that --(A1) is identical
> >>to (-1)*(-1)*A1?
> >>
> >>Dave Peterson wrote:
> >>
> >>>type
> >>>=row(indirect("1:11"))
> >>>in a cell
> >>>then hit F2 followed by F9
> >>>
> >>>You'll see that it does return an array.
> >>>
> >>>
> >>>=mid() returns a text string. If that string is numeric, then -- converts it to
> >>>a number. The first negative converts it to a negative number and the second
> >>>changes the sign to non-negative.
> >>>
> >>>xirx wrote:
> >>>
> >>>
> >>>>Hi!
> >>>>
> >>>>Can someone please explain how this monster works?
> >>>>
> >>>>If the value of A1 is "somenum1or2", it really returens a "1".
> >>>>I wonder why..
> >>>>
> >>>>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
> >>>>
> >>>>"1:"&LEN(A1) yields "1:11".
> >>>>
> >>>>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
> >>>>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
> >>>>Row(indirect("x:y")) for some number x and y just yields the
> >>>>lower one. Strange.
> >>>>
> >>>>Yes, this is maybe the wrong approach to understand this formula,
> >>>>because it is an array formula. I know a little bit of those.
> >>>>But not enough to understand this monster. I only have a vague
> >>>>idea:
> >>>>
> >>>>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
> >>>>an array of all charakters in A1 by calculating MID(A1,x,1) for
> >>>>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
> >>>>values and the MATCH looks for the first TRUE value and returns its
> >>>>index in the array. Finally, the MID returns just that char. Easy?!?
> >>>>
> >>>>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
> >>>>an array of characters of the string in A1?
> >>>>
> >>>>And what the hell does "--" mean / do?
> >>>>
> >>>>Please enlight me...
> >>>>
> >>>>KL wrote:
> >>>>
> >>>>
> >>>>>Hi,
> >>>>>
> >>>>>Try this:
> >>>>>
> >>>>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
> >>>>>
> >>>>>Regards,
> >>>>>KL
> >>>>>
> >>>>>
> >>>>>"xirx" <xirx@gmx.de> wrote in message
> >>>>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hi!
> >>>>>>
> >>>>>>The seach/3 function searches for 'find_text' in
> >>>>>>'within_text', starting at the 'start_nun's character:
> >>>>>>
> >>>>>>SEARCH(find_text,within_text,start_num)
> >>>>>>
> >>>>>>I need to find the first digit in a string. I guess,
> >>>>>>search/3 does neither support regular expresseions,
> >>>>>>no does it allow a function instead of a constant
> >>>>>>'search_text'.
> >>>>>>
> >>>>>>One way to find the first digit is this monster:
> >>>>>>
> >>>>>>=MIN(
> >>>>>>IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
> >>>>>>IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
> >>>>>>)
> >>>>>>
> >>>>>>Any more efficient way to do a
> >>>>>>
> >>>>>>search('[0-9]';D2) or search(isnumber();D2)
> >>>>>>
> >>>>>>?
> >>>>>
> >>>>>
> >>>>>
> >

--

Dave Peterson

And another question:

What do F2 and F9 do?


Dave Peterson wrote:

> type
> =row(indirect("1:11"))
> in a cell
> then hit F2 followed by F9
>
> You'll see that it does return an array.
>
>
> =mid() returns a text string. If that string is numeric, then -- converts it to
> a number. The first negative converts it to a negative number and the second
> changes the sign to non-negative.
>
> xirx wrote:
>
>>Hi!
>>
>>Can someone please explain how this monster works?
>>
>>If the value of A1 is "somenum1or2", it really returens a "1".
>>I wonder why..
>>
>>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
>>
>>"1:"&LEN(A1) yields "1:11".
>>
>>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
>>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
>>Row(indirect("x:y")) for some number x and y just yields the
>>lower one. Strange.
>>
>>Yes, this is maybe the wrong approach to understand this formula,
>>because it is an array formula. I know a little bit of those.
>>But not enough to understand this monster. I only have a vague
>>idea:
>>
>>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
>>an array of all charakters in A1 by calculating MID(A1,x,1) for
>>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
>>values and the MATCH looks for the first TRUE value and returns its
>>index in the array. Finally, the MID returns just that char. Easy?!?
>>
>>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
>>an array of characters of the string in A1?
>>
>>And what the hell does "--" mean / do?
>>
>>Please enlight me...
>>
>>KL wrote:
>>
>>>Hi,
>>>
>>>Try this:
>>>
>>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
>>>
>>>Regards,
>>>KL
>>>
>>>
>>>"xirx" <xirx@gmx.de> wrote in message
>>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
>>>
>>>
>>>>Hi!
>>>>
>>>>The seach/3 function searches for 'find_text' in
>>>>'within_text', starting at the 'start_nun's character:
>>>>
>>>>SEARCH(find_text,within_text,start_num)
>>>>
>>>>I need to find the first digit in a string. I guess,
>>>>search/3 does neither support regular expresseions,
>>>>no does it allow a function instead of a constant
>>>>'search_text'.
>>>>
>>>>One way to find the first digit is this monster:
>>>>
>>>>=MIN(
>>>> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
>>>> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
>>>> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
>>>> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
>>>> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
>>>> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
>>>> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
>>>> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
>>>> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
>>>> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
>>>>)
>>>>
>>>>Any more efficient way to do a
>>>>
>>>>search('[0-9]';D2) or search(isnumber();D2)
>>>>
>>>>?
>>>
>>>
>>>
>

If you have a cell selected, F2 will put you in Edit mode. (also if you're on
some dialogs--it'll toggle between enter|edit|and point.)

F9 tells excel to recalculate.

So in this example, F2 put you into edit mode. Then the following F9 will
calculate that cell--essentially, it copies|paste special|values. But in this
case, since the formula returned an array you'd see:

={1;2;3;4;5;6;7;8;9;10;11}




xirx wrote:
>
> And another question:
>
> What do F2 and F9 do?
>
> Dave Peterson wrote:
>
> > type
> > =row(indirect("1:11"))
> > in a cell
> > then hit F2 followed by F9
> >
> > You'll see that it does return an array.
> >
> >
> > =mid() returns a text string. If that string is numeric, then -- converts it to
> > a number. The first negative converts it to a negative number and the second
> > changes the sign to non-negative.
> >
> > xirx wrote:
> >
> >>Hi!
> >>
> >>Can someone please explain how this monster works?
> >>
> >>If the value of A1 is "somenum1or2", it really returens a "1".
> >>I wonder why..
> >>
> >>With A1="somenum1or2", "1:"&LEN(A1) yields 11.
> >>
> >>"1:"&LEN(A1) yields "1:11".
> >>
> >>However: indirect("1:11") yields 0. And Row(indirect("1:11"))
> >>yields 1. And Row(indirect("88:11")) yields 11. It seems, that
> >>Row(indirect("x:y")) for some number x and y just yields the
> >>lower one. Strange.
> >>
> >>Yes, this is maybe the wrong approach to understand this formula,
> >>because it is an array formula. I know a little bit of those.
> >>But not enough to understand this monster. I only have a vague
> >>idea:
> >>
> >>I assume that the --MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) creates
> >>an array of all charakters in A1 by calculating MID(A1,x,1) for
> >>all x from 1 to LEN(A1). ISNUMBER then maps these chars in TRUE/FALSE
> >>values and the MATCH looks for the first TRUE value and returns its
> >>index in the array. Finally, the MID returns just that char. Easy?!?
> >>
> >>What I don't understand is: How does ROW(INDIRECT("1:"&LEN(A1)) return
> >>an array of characters of the string in A1?
> >>
> >>And what the hell does "--" mean / do?
> >>
> >>Please enlight me...
> >>
> >>KL wrote:
> >>
> >>>Hi,
> >>>
> >>>Try this:
> >>>
> >>>=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)
> >>>
> >>>Regards,
> >>>KL
> >>>
> >>>
> >>>"xirx" <xirx@gmx.de> wrote in message
> >>>news:429c3975$0$298$4d4ebb8e@read.news.de.uu.net...
> >>>
> >>>
> >>>>Hi!
> >>>>
> >>>>The seach/3 function searches for 'find_text' in
> >>>>'within_text', starting at the 'start_nun's character:
> >>>>
> >>>>SEARCH(find_text,within_text,start_num)
> >>>>
> >>>>I need to find the first digit in a string. I guess,
> >>>>search/3 does neither support regular expresseions,
> >>>>no does it allow a function instead of a constant
> >>>>'search_text'.
> >>>>
> >>>>One way to find the first digit is this monster:
> >>>>
> >>>>=MIN(
> >>>> IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
> >>>> IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
> >>>>)
> >>>>
> >>>>Any more efficient way to do a
> >>>>
> >>>>search('[0-9]';D2) or search(isnumber();D2)
> >>>>
> >>>>?
> >>>
> >>>
> >>>
> >

--

Dave Peterson