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
779 Views

Similar Articles

[PageSpeed] 54

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:

Pound (#) signs instead of text
I entered text in a cell that is formatted for text and wrap. When I hit enter it shows as pound signs. I've tried making the cell wider and taller but that didn't help. I used the format brush to copy the format down from the cell above...still doesn't work. If you select the cell, does the text show up in the formula bar? -- Gary''s Student "mommyo" wrote: > I entered text in a cell that is formatted for text and wrap. When I hit > enter it shows as pound signs. I've tried making the cell wider and taller > but that didn't help. ...

zip codes not being saved as text in CSV format
I need my file to be saved in CSV format (to import into another application). When I format the zip codes as Text or Special(zip code), they are correct in XLS format, but when I open the file in CSV format they switch back to general. Any solution? work around? Thanks so much for your help in advance!! Melissa Change the file extension to .txt and use the text import wizard to treat the column as text (last dialog) -- Regards, Tom Ogilvy "MelissaM" <MelissaM@discussions.microsoft.com> wrote in message news:B5A02627-810E-4234-8247-267D9B12FA29@microsoft.com... >...

Excel changes the values of my numbers
I have coded a simple user form in Excel that calculates a balance an assigns the proper values to cells in a worksheet. What I don' understand is that when I enter a number into the user form, the valu of that number changes once it gets assigned to the cell in th worksheet. For example, if I enter the number 542.73 ,the user for assigns the number to the proper cell in the worksheet but it shows u as 542.72998046875. It should show up as 542.73 because that's what entered. I am calculating a running balance so my calculations aren' coming out right. Sometimes they are off ...

How does one update and transfer values between cells and not the. #2
I have a spreadsheet open that has several layers of formulas. I need to take a result of a formula in cell A and use that in the argument of another formula in cell B. Currently I have copied the contents of cell A to cell B and so the prior formula not the result is in the cell and as such the new argument can't update the changes to the value of cell B because between the two formulas I am multiplying and dividing by the same number. JC Just refer to the cell A in your cell B formula. In A1 you have =12*4 for a return of 48 In B1 enter =A1*2 for a return of 96 Gord Dibben...

saving to text file
I am encountering a strange error message when i try to save my Excel 2000 file to a tab-delimited text file. I am receiving the following message: "'[File Name].xls' cannot be accessed. The file may be read-only or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding." None of the cases stated in the error messages are true of these files. If you have any suggestions to rectify this situation, it would be greatly appreciated. Thank you. If you use windows explorer to copy your .xls file to your local hard...

How can we get the subtraction of values in first 2 columns in the 3rd column
Hi Friends Greetings. If I have the following 500 row excel file, how can we get the subtraction of first two columns(Col3)?. I couldn't find any subtraction function in microsoft excel 2000. Col1 Col2 Col3(I want the subtraction of Col1-Col2) ------ -------- ---------- $50 $30 ??(I want $20 here) $100 $40 ??(I want $60 here) $150 $25 ??(I want $125 here) --- ---- --- --- ---- --- Thank you Jane Humbrey Hi Jane! Use: =A1-B1 Copy down parallel to your data. -- -- Regards Norman...

search file on computer remote
Hi, i would like to create script VBS, which search an file in remote computer. This is my script, but doesn't work: Set objFSO = CreateObject("Scripting.FileSystemObject") strComputer = pcremote if objFSO.FileExists("\\" & strComputer &"D:\H4PDF\toto2.txt") Then MsgBox "Fichier toto OK", vbExclamation Else MsgBox "Fichier toto KO", vbExclamation End If Loop Best Regards, Robby "Robby" <fabrice@discussions.microsoft.com> wrote in message news:uh3ch0SELHA.5736@TK2MSFTNGP02.phx.gbl... > Hi...

Need to Sum Non Numerical Columns and Create a Chart in Office 2003 Excel/Power Point
Hi, I need to create a chart using Excel 2003 and or PowerPoint 2003. The nonnumeric data is stored in a multiple column spreadsheet. In one of the columns (A) the cells are populated with letters (BT or PP or RD) I need a dynamic total of the number of populated cells (not all cells are populated) in this column, then I'd like to compare the total number of entries to another column (B) that is populated w/ Xs, Rs and Ns. These letters refer to the status of the PKI certificates i.e., received, requested or needed. So, I'd like these values to be a dynamic chart that'l...

Text to numbers #2
I am converting the following to numbers using =value(LEFT(D117,LEN(D117)-1)) 4" 3/4" 2" All works fine except the fraction keeps turning into a date value? I try formatting the cells but this does not work either? Any advice -- Ray Ray, Try this =VALUE(IF(ISNUMBER(FIND("/",D6)),"0 ","")&LEFT(D6,LEN(D6)-1)) -- HTH Bob Phillips "Ray" <Ray@discussions.microsoft.com> wrote in message news:4D8C1083-6C0C-4293-813D-C86D79BCFE86@microsoft.com... > I am converting the following to numbers using =value(LEFT(D117,LEN(D117)-1)...

Quest: Getting int(base 10) value from hex formated CString...
Can I convert a Hex Str("0x" included) to int value so I can set it to a int variable. The Hex Str is formated by... HexStr.Format("0x%08x", iDECValue); //iDECValue is temp and goes out of scope... and I want to format another str obj by... AnotherStr.Format("0x%08x", (iNewConvertHexStrToDECValue + iAnotherINTVAL)); So is there a API/C function like atoi but works with Hex number that I can use for this... Any help... I don't know of a pre-existing function but this crude example should work (and it can probably be improved upon): int hatoi(const ...

memory alignment of structs in non trivial deriviation....
i've the following: struct SA { DWORD dwA; BYTE bA; int iA; }; struct SAX : public SA { DWORD dwAx; CString scAtest; void Afoo(); }; struct SB : public SA { DWORD dwB; BYTE bB; int iB; }; struct SBX : public SB { DWORD dwBx; CString scBtest; void Bfoo(); }; SBX sbxTest; ________________________________________________ what i want to have in memory at &sbxTest is this: [ DWORD dwA ] [ BYTE bA ] [ int iA ] [ DWORD dwB ] [ BYTE bB ] [ int iB ] [ rest of SAX ] [ rest of SBX ] how do i force the compile...

Windows 7 non-Beta joining to SBS2008 domain
I finally joined the computers I needed the old way. The http://connect wizard just doesn't work for me. It tells me that the computer I'm trying to join doesn't meet the requirements. Both the server and the workstations are up to date with all the MS patches. Any idea how to fix it? yaro This is a multi-part message in MIME format. ------=_NextPart_000_0455_01CA820D.E4FCD100 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You need to install SBS Update Rollup (UR) 3 on the server. You should = then be able to...

CHECK BOXES IN SEARCH FORM
This my second posting of a problem I errantly checked as answered. I am using Allen Browne’s great Search Form [ http://www.allenbrowne.com/ser-62.html ]. The record source for this form is a query of numerous tables. My problem is with a concatenated field in my query that converts seven check boxes to their field namesakes. The fields are alpha-numeric; 24A; 24B; 67E; 105; 111B; 202 & PELL – pretty random stuff. Any number of the fields can be selected. Therefore, the queried records field “Sections” is just as random and look like this: 24A, 24B or 24A or 111B, 202 ...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

How do I formulas to convert values into numeric order?
How do I convert the 3 cells below in the correct order columns to the 3 cells in Numeric order columns, using excel formulas. or how to convert the 3 individual cells into one cell in numeric order? Correct Value,,,,,,,,,,,,,Numeric Order 9,,,,,,2,,,,,,6,,,,,,,,,,,,,,,,2,,,,,,,6,,,,,,,,9 Correct Value,,,,,,,,,,,,,,,,,,,Numeric Order 9,,,,,,2,,,,,,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,6,9 Thank you ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Select the 3 cells you ...

Publisher 2000
Hi, I wonder whether anyone can help me please. I am using Publisher 2000 and have created a speech bubble from the Custom Tools, I then want to add some text into this speech bubble, so have added a text frame and moved it on top of the speech bubble and made it transparent. My problem is that I want the text to fit within the shape of the speech bubble, rather than the left aligned text within the text frame. Not sure if this is clear enough, but I want all of the text to fit within the speech bubble shape. I have searched everywhere and can find nothing that helps. Have you tried ...

Requesting Data from Exchange server.....runs constantly
Within minutes of starting Outlook, it starts to request data from the Exchange server. Other than cancelling the request, which allows me to use Outlook for another 5 minutes before it pops up again, what is it trying to do and why does it never end? I have left it running for hours and there is no data received. Outlook 2002? Make sure Instant Messaging is *not* enabled (Tools/Options/Other). SueM >-----Original Message----- >Within minutes of starting Outlook, it starts to request >data from the Exchange server. Other than cancelling the >request, which allows me to u...

Excel 2003 / Send To / Mail Recipient / body text is removed
When a user is working in Excel 2003 and hits File.. Send To.. Mail Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email as an Attachment. If they type any text into the body of the email it is replaced with <<...>> How can I change this ? If you don't get a good reply here, you may want to post in one of the .Outlook newsgroups. jmaynard2 wrote: > > When a user is working in Excel 2003 and hits File.. Send To.. Mail > Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email > as an Attachment. > > If they typ...

IMAP errors
Outlook 2003 configured for IMAP email. Some emails do not show the reply text message, though it shows just about everything else. School tech support recommended changing email options to download complete items, including attachments. This did not help. The school is using Sun's OneMessenger Express email service. Thanks ...

OWA Contact search
Hi, We are using OWA 2003, We would like to configre that all our users will search in OWA in contact folder and no GAL by default We don't want that our uses will go to Contact Options >> Check names first in and change to Contacts, is there any option to change this automaticaly for all our users ? Thanks ...

copy value from the cell above to the cell below in a column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below, all the way down the column at once. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Ctrl-D or edit-fill-fill down, works one at a time. But there is a way of copying each employee's name to the blank cell below all at once, without doing it individually. can anyon help? thanks See answers to yo...

Highest Value / Circular Reference Help
I use a spreadsheet to track stock portfolios and determine stop orders. I place the Stop Orders every 30 days. To determine the Stop Order Price, I need to take the highest value of either (1) a certain percentage (say 15%) of the cost basis, (2) a certain percentage (say 15%) of the current value, or, (3) the last stop order price. Since stock prices fluctuate, the "last stop order price" could be higher than "15% below the current value", but I can not set it up to automatically track the highest value without creating a circular reference. Following is a shor...

Syntax for total text box in footer?
I have a report that shows the results of our customer satisfaction questionnaires. One of the fields is "Recommend" and the value is a Y/N checkbox. I want to create a textbox in the report footer that counts the number of entries where the Recommend value is Y. I have tried entering this as the control source: =Count([Recommend]="Y") which I thought seemed a reasonable attempt, but no good. Would be very grateful for your advice Many thanks CW Try =Abs(Sum([Recommend])) How it works A Check box has a value of 0 (unchecked) or -1 (Checked). So we sum the values and g...

Sorting text field like a number field
I have a text field that stores numbers that may occassionally have letters after them. I need to be able to sort this field as if it is a number field even though it holds text. Unfortunately, that is not something I have an option to change, but I have to find a way to work with it. The problem comes with the numbers 1-9 falling in the wrong place with the text sort. Does anyone have a suggestion on how to correct that in the sorting? The only thing I came up with was very messy. That was to use nested if statements to handle 1-9. I am open to suggestions. Thanks, Beth Type t...

A number formatted as text is not recognized in a vlookup.
In a multi-tabbed workbook, we have product numbers (some with leading zeros - some with alpha-numeric values) on one tab along with other product information. On another tab, using a vLookup function that worked in Office 2000, the product numbers being typed in are not matching the product numbers on the other tab. How can we resolve this? My product numbers must be formatted as text to avoid dropping leading zeros. ...