text search for a non-constant value?

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)

?
0
xirx (45)
5/31/2005 10:16:21 AM
excel 39879 articles. 2 followers. Follow

14 Replies
774 Views

Similar Articles

[PageSpeed] 24

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


0
5/31/2005 11:12:31 AM
....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)
>>
>> ?
>
> 


0
5/31/2005 11:15:58 AM
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).

0
hrlngrv (1990)
5/31/2005 5:06:47 PM
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)
>>>
>>>?
>>
>>
> 
> 
0
xirx (45)
6/1/2005 12:33:01 PM
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)
 >>>
 >>>?
 >>
 >>
 >
 >
0
xirx (45)
6/1/2005 12:33:41 PM
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)
> >>>
> >>>?
> >>
> >>
> >
> > 


0
6/1/2005 12:38:19 PM
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)
>>
>>? 
> 
> 
> 
0
xirx (45)
8/12/2005 4:06:54 PM
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
0
petersod (12004)
8/12/2005 10:49:28 PM
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)
>>>>
>>>>?
>>>
>>>
>>>
> 
0
xirx (45)
8/14/2005 10:09:46 PM
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)
>>>>
>>>>?
>>>
>>>
>>>
> 
0
xirx (45)
8/14/2005 10:10:36 PM
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
0
petersod (12004)
8/15/2005 2:00:26 AM
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
0
petersod (12004)
8/15/2005 2:09:13 AM
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)
>>>>>>
>>>>>>?
>>>>>
>>>>>
>>>>>
> 
0
xirx (45)
8/15/2005 10:30:52 AM
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
0
petersod (12004)
8/15/2005 12:07:30 PM
Reply:

Similar Artilces:

2k4 Databases constantly becoming corrupt
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: imap Good day, <br><br>We have several users who are reporting corrupt Databases every couple days. Both users have large databases (25GB). Both users are using iMacs connected to Exchange 2003. <br><br>I have removed and rebuilt the databases. I have removed com.microsoft.OfficeNotifications.plist on both machines. I have deleted identities and reconfigured entourage to connect to the server. I have updated Entourage to the latest update. We have deleted large quantities ...

Graph with text
I have a status field "Open, Working, On-Hold, or Done" and a second column for project name. I want to create a chart that shows the project name on the x-axis and the status on the Y axis. THIS IS A SECOND REQUEST, please help. DDBeards You need to convert the status to a numerical scale (1,2,3,4), plot X=project vs Y=numerical status, and finally fix up the vertical axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ DDBe...

Select All Pictures and Change Wrapping Style to Behind Text
I am trying to get a macro that can change the wrapping style to behind text for all the pictures in the document. It would also be great if I could get this macro to change a lot of other properties too. For example, I want all the photos behind text, center horizontal alignment, height = 4", width = proportional. It would also be great if this macro could add a text box under each picture which was grouped with its respective picture. Any advice on any of this would be greatly appreciated. Funny you should ask this now. I was just fighting with the same problem yeste...

How do I arch a line of text within a banner?
I am trying to design a banner that will have two lines of text. Rather than both lines being straight, I would like to arch the top line over the second line so that I can insert artwork between the two lines of text. Can this be accomplished with this program? If not, is there a program available the I could use instead. Thank You. *Brandon* numberonesignguy wrote: > I am trying to design a banner that will have two lines of text. > Rather than both lines being straight, I would like to arch the top > line over the second line so that I can insert artwork between the > two...

How to do a global search and replace of a font in a Pub 2003 file
I have a long document given to me with text boxes that were not created using Font Schemes. Everywhere there is Times New Roman, I have to change it to another font. This is so easy in Word, but I can't figure it out in Publisher. Help! When you apply a font scheme to a text box, all the text boxes should change. On the toolbar, Format, Font Schemes. -- Mary Sauer http://msauer.mvps.org/ "srhamm" <srhamm@discussions.microsoft.com> wrote in message news:8CF4DA6A-52FA-43F7-84F2-4513CCCD6E99@microsoft.com... > I have a long document given to me with...

Why doesn't the text fit its self around my graphics?
I'm using Publisher 2003. I've inserted a clipart but the text goes under the graphic. I want the text to flow around the graphic. PLEASE help me get the text to flow around my graphic. I've used Pub 2000 and haven't run into this problem....can't find anything to fix this! Thanks in advance!!! -- Kelvin Using Pub 2000 here also Kelvin, Give this a try. Put the clipart on top layer. Use the icons in the tool bar that look like three little squares. One sends the active text or image box to the back and the other brings them to the front. -- Don -------- Vancou...

Text to Columns
500+ sentences, I need to seperate (text to columns) after the first "-" only. There are subsequent "-" but I only want it seperated at the first. And when I seperate it in the second column I do not want a space preceeding the text. Can you give advice on this, any help would be much appreciated! Thanks Sample Data AIR TAXI - An aircraft operator who conducts operations for hire or compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer passenger seats and a payload capacity of 7,500# or less. An air taxi operates on an on-dem...

I can not copy text into a brochure without another textbox opening
Hi all, Every time I try to copy and past into a textbox already created by a publisher template, another textbox is created by defalut to add the text into it. I want to just add the text into the textbox already within the template like you could with Publisher 2000. Thanks, Kelsey Is your cursor inside the textbox when you paste? If you just paste Publisher will create a new text box. If this isn't the answer create a new textbox, paste and delete the original text box. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

selecting text and spikes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I want to do the usual: insert the cursor, click/drag to highlight, press F3 to copy, then paste. <br><br>But when I click, Word selects multiple words or lines, when I attempt to copy (F3), the text deletes. When I look under the Edit menu, it says &quot;Undo spike.&quot; <br><br>Yikes, spikes? what am i doing? or what is word doing? <br><br>BTW, when i tried to enter text in Word help, i couldn't until i restarted and reset the pram. F3 is the Spike key, not ...

Writing Text File with code UTF-8
Hello Does somebody know how to write a Text File with code UTF-8 from Dexterity? I know that are 3 functions to write text files: TextFile_Writeline() TextFile_WriteDOS() TextFile_WriteText() But there is not therein a parameter to achieve text file be written with code UTF-8. I=92ll appreciate your guide. Best regards. ------=_NextPart_0001_A41C49B3 Content-Type: text/plain Content-Transfer-Encoding: 7bit Dexterity does not support double byte or unicode. You might need to use COM or win32 dll calls to get the OS to write the file for you. The difference between WriteLine and WriteDOS i...

New History Item, Mail Message sends all messages as Rich Text Format
We are using Business Contact Manager 2007 with Outlook 2007. We are not using Exchange server as a backend, just plain old pop3/smtp mail servers. Problem we are having is when we open up an account, Click on New History Item, Mail Message, and if we send an attachment with that, the person that receives the message gets it in Rich Text format. If we right click on the Email address itself and click on Send Mail. The message will be sent out fine. Right click on the message and looking at the properties of the email address has the internet format listed as "Let Outlook decide the ...

Passing values to an array
Can I pass values (e.g. a concatenated string of days) to an array from another function? This sub works: Sub xArray1() Dim MyWeek, MyDay MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun") For Each MyDay In MyWeek Debug.Print MyDay Next MyDay End Sub How could I make something like this work? Sub xArray2(MyWeekValues as Variant) Dim MyWeek, MyDay MyWeek = Array(MyWeekValues) For Each MyDay In MyWeek Debug.Print MyDay Next MyDay End Sub The ParamArray keyword...

GP10-SP3-RM-CashReceipt-Can not insert the value NULL
Hi , I am getting the following error message when trying to view customer transactions (Sales>Inquiry>By customer>View transactions in orginating currency) can not insert the value NULL into column 'ORTRXAMT',table 'tempdb.dbo##1391112,column dones not allow nulls.INSERT fails. The stored procedure rmTransactionInquiry returned the following results:DBMS515,Microsoft Dynamics GP:0 Regards, Sandy Hi Sandy Have you tried running check links? It sounds like you have an corrupt record in the temp table and the check links should fix it. -- Fliehigh &q...

Text box set to memo vs. Lines Coded on a report
I have a report which looks like a legal pad with lines. 24 actual spaces. The number of lines can not change. I have duplicated a form from Excel to eliminate using it altogether. So it looks just like the Excel Report. I coded the lines into the report. I am using a text box with data type set to memo. I have several issues. 1. If the data entered into the textbox from my form fits in to the text box on one line, then there is no problem. I can just move the page footer to more or less the right position to allow it to fit inside the coded lines. (It is not exact, but close...

PivotTable "show data value as" question
Hi all, I have a problem about using Pivot Table in Excel 2003: In a pivot table I have a date field, grouped by year and month, as the row field. The data field of the table is anything that can be summed. Now when I tried to display the data field value as "Difference from previous month", I found that January of each year is always blank, ideally the data value in January should be display as the difference between the actual January value minus December value of the last year. Any tricks to show them? Thanks a lot. Frederick Chow Hong Kong. If you group the dates by ...

Search help
Hi, Tried a few functions for this but i cant get it to do what im looking for! I have 8 worksheets and Im trying to get a specific reference from A1 on sheet1. The other 7 pages has all the info in column A I want B2 to tell me the reference it found out of the text in A1, if its possible Example: sheet1 A1 = abc toy car pt no 789DIN1 on sheet2 A1= 785DIN1955 A2= 786DIN1966 A3= 789DIN1977 This is the closest reference on all of my worksheets so i want Sheet1 B2 = 789DIN1977 If it cant be done then i would like the same if Sheet1 A1= abc toy car pt no 789DIN1977 Ive tried usi...

TreeView Control Search
What is the simplest way to Search a TreeView Control? I need to find the a specific value in the TEXT of the TreeView. I've looked at: http://127.0.0.1:47873/help/1-7024/ms.help?method=page&id=M:SYSTEM.WEB.UI.WEBCONTROLS.TREEVIEW.FINDNODE(SYSTEM.STRING)&product=VS&productversion=100&locale=en-US&topiclocale=EN-US&topicversion=100&SQM=2 but the ValuePath and ValuePathText aren't available. ...

Is it possible to count distinct values in a Pivot Table?
I have a pivot table that gets its data from Access. My table in Access has about 20,000 records, with 1,500 unique account numbers having 10 to 20 pieces of account detail info each. When I use the pivot table, putting any distinct item in the data portion of the table and counting it gives me a count of 20,000. But there seems no way within the table to say "count only the distinct items" which would give me a count of 1,500. Is there a way to do that, or do I need to restrict such queries to MS Query or Access? I suppose what I want is the count I would get if I manually adde...

Can't send if text entered in message body
I'm having a strange send error. If I just put the recipient address and a subject, the email will send. If I add ANY text to the message, when I click "Send & Receive" it starts to send, I get the progress bar, then it just sits there until it errors out. I'm totally stumped by this one...any help out there? Thanks, Rod ...

Text wrap spec
People, I thought I understood that the text wrap limit for any given cell was 1024 and indeed when I look at the specification for Excel, it states that only 1024 text characters will display in a cell. However, I have the following condition (Excel 2003 running under Windows XP Pro). I have a column that is 35 wide. I entered repeating text in four paragraphs. The total length of my string is 1093 characters (verified using the Len function). The last word wrap occurs at character 1032, definitely beyond the 1024 limit. The total number of characters that shows in the cell is 1074 an...

Import/Export Text Specification
Is it possible to copy a single "Import/Export Text Specification" from one mdb to another? From the file that does not have the specification, File | Get External Data .... | Import, then choose .mdb file type and choose the file that has the specification in it, then click Options button in the wizard window and select the checkbox for importing import/export specifications. -- Ken Snell <MS ACCESS MVP> -- Ken Snell <MS ACCESS MVP> "JimP" <jpockmire@houston.rr.com> wrote in message news:4656fdde$0$9933$4c368faf@roadrunner.co...

limit range to unique values
I am looking for a way to limit a named range to unique values. So that if a value is entered that already exists in the range the value that already exists would become empty and the new location would hold the unique value. I got something sort of working with the selection change event but it took too long to cycle through each cell in the range to make sure it did not equal the target value. was not pretty. The range is only like 50 cells. I am looking for advise on how to make it work. Thanks Include your efforts to date If desired, send your file to my address be...

Word 2007 Is Non-Functional
HELP! Yesterday, Word 2007 worked fine. Today, after some Microsoft auto updates were applied (one was a security update to Office (KB978380)) it fails every time. If I start Word and try to save or open a file - crash ("Microsoft Word has stopped responding"). My system has a QX-9650 Quad-Core with 8GB of memory and dual video cards in a quad-SLI configuration (2GB VRAM). I have one application open (Word) so system resources are not an issue. I'm running Vista x64. I went to the Technet site and found an article on Word continually crashing but neither ...

Can't use SendMessage...WM_PASTE with a regular Text Box
Anyone have any idea why SendMessage ctl.hwnd, WM_PASTE, 0, 0 isn't working from a regular Access text box (form is opened normally, not in dialog more)? I realize I can just use docmd.RunCommand acCmdPaste. But I have a reason for needing to do it this way. When I run the code I get the message, "Object doesn't support this property or method" (error 438). Any ideas why this is happening? Thanks! Neil Here is the code I'm using: Private Declare Function SendMessage _ Lib "user32" _ Alias "SendMessageA" ( _ ByVal hwnd As Long, _ ByV...

How can i resize grouped text and maintain proportions?
I have 2 words that are grouped together and i want to resize them without having to change the font size. How do you do this publisher? How do you resize something without changing the size? (Can I do that with jeans? Smaller size without losing weight - woo hoo!) -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:4B868D80-6A01-4F5E-B045-59803522DCDC@microsoft.com... >I have 2 words that are grouped together and i want to resize them without > h...