text search for a non-constant value?

[xirx]

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)

?

[KL]

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)
>
> ?

[KL]

....sorry, forgot to mention that this is an ARRAY formula (should be entered
with Ctrl+Shift+Enter)

Regards,
KL

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OxBBJHdZFHA.3356@TK2MSFTNGP15.phx.gbl...
> 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)
>>
>> ?
>
>

[xirx]

Thank you very much. However, I only get a syntax error
for this formula. (Well, due to language settings, I need
to replace each comma "," by a semicolon ";").


KL wrote:

> ...sorry, forgot to mention that this is an ARRAY formula (should be entered
> with Ctrl+Shift+Enter)
>
> Regards,
> KL
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OxBBJHdZFHA.3356@TK2MSFTNGP15.phx.gbl...
>
>>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)
>>>
>>>?
>>
>>
>
>

[xirx]

Thank you very much. However, I only get a syntax error
for this formula. (Well, due to language settings, I need
to replace each comma "," by a semicolon ";").


KL wrote:

> ...sorry, forgot to mention that this is an ARRAY formula (should be
entered
> with Ctrl+Shift+Enter)
>
> Regards,
> KL
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OxBBJHdZFHA.3356@TK2MSFTNGP15.phx.gbl...
>
>>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)
>>>
>>>?
>>
>>
>
>

[KL]

Hi,

Well, you may need to change something else then, as the formula works to me
as posted. If you so wish just post the exact formula you are inputting in
your machine and tell us in what language it is (just in case it is not
obvious).

In any case, I would go wirth the formula suggested by Harlan Grove below.

Regards,
KL

"xirx" <xirx@gmx.de> wrote in message
news:429dab26$0$301$4d4ebb8e@read.news.de.uu.net...
> Thank you very much. However, I only get a syntax error
> for this formula. (Well, due to language settings, I need
> to replace each comma "," by a semicolon ";").
>
>
> KL wrote:
>
> > ...sorry, forgot to mention that this is an ARRAY formula (should be
> entered
> > with Ctrl+Shift+Enter)
> >
> > Regards,
> > KL
> >
> > "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> > news:OxBBJHdZFHA.3356@TK2MSFTNGP15.phx.gbl...
> >
> >>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)
> >>>
> >>>?
> >>
> >>
> >
> >

[xirx]

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]

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

[xirx]

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]

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

[xirx]

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]

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

[Harlan Grove]

xirx wrote...
....
>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'.
....

Easiest and most efficient would be

=MIN(FIND({0;1;2;3;4;5;6;7;8;9},x&"0123456789"))

which doesn't need to be entered as an array formula. If there are no
decimal numerals in x, the result will be > LEN(x).