text search for a non-constant value?

X

xirx

Hi!

The seach/3 function searches for 'find_text' in
'within_text', starting at the 'start_nun's character:

SEARCH(find_text,within_text,start_num)

I need to find the first digit in a string. I guess,
search/3 does neither support regular expresseions,
no does it allow a function instead of a constant
'search_text'.

One way to find the first digit is this monster:

=MIN(
IF(ISNUMBER(SEARCH(0;D2));SEARCH(0;D2);9999);
IF(ISNUMBER(SEARCH(1;D2));SEARCH(1;D2);9999);
IF(ISNUMBER(SEARCH(2;D2));SEARCH(2;D2);9999);
IF(ISNUMBER(SEARCH(3;D2));SEARCH(3;D2);9999);
IF(ISNUMBER(SEARCH(4;D2));SEARCH(4;D2);9999);
IF(ISNUMBER(SEARCH(5;D2));SEARCH(5;D2);9999);
IF(ISNUMBER(SEARCH(6;D2));SEARCH(6;D2);9999);
IF(ISNUMBER(SEARCH(7;D2));SEARCH(7;D2);9999);
IF(ISNUMBER(SEARCH(8;D2));SEARCH(8;D2);9999);
IF(ISNUMBER(SEARCH(9;D2));SEARCH(9;D2);9999)
)

Any more efficient way to do a

search('[0-9]';D2) or search(isnumber();D2)

?
 
K

KL

Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL
 
K

KL

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

Regards,
KL

KL said:
Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL


xirx said:
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)

?
Click to expand...
Click to expand...
 
H

Harlan Grove

xirx wrote...
....
I need to find the first digit in a string. I guess,
search/3 does neither support regular expresseions,
no does it allow a function instead of a constant
'search_text'.
Click to expand...
....

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).
 
X

xirx

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

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

Regards,
KL

Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL


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)

?
Click to expand...
Click to expand...
Click to expand...
 
X

xirx

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

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

Regards,
KL

Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL


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)

?
Click to expand...
Click to expand...
Click to expand...
 
K

KL

Hi,

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

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

Regards,
KL

xirx said:
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 ";").

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

Regards,
KL

Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL



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)

?
Click to expand...
Click to expand...
Click to expand...
 
X

xirx

Hi!

Can someone please explain how this monster works?

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

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

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

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

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

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

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

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

Please enlight me...


Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL


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)

?
Click to expand...
Click to expand...
 
D

Dave Peterson

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

You'll see that it does return an array.


=mid() returns a text string. If that string is numeric, then -- converts it to
a number. The first negative converts it to a negative number and the second
changes the sign to non-negative.
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...
Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL


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)

?
Click to expand...
Click to expand...
Click to expand...
 
X

xirx

Dave,

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

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



Dave said:
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.
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...
Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL




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)

?
Click to expand...
Click to expand...
Click to expand...
 
X

xirx

And another question:

What do F2 and F9 do?


Dave said:
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.
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...
Hi,

Try this:

=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1)

Regards,
KL




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)

?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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

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

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

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

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

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

(as long as A1 looks like a number.)
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 said:
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.
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




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)

?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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

F9 tells excel to recalculate.

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

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



And another question:

What do F2 and F9 do?

Dave said:
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.
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




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)

?
Click to expand...
Click to expand...
Click to expand...
 
X

xirx

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 said:
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.)
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 said:
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





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)

?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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



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 said:
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.)
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





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)

?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Average problem 1
Error when no records meet criteria 2
TO mr t.valko... 7
Error when no records meet criteria - repost 3
Add SUMPRODUCTS with Multiple Criteria 14
Index match with multiple criteria(different than usual) 3
excel not recognising numbers containing 0.1calculated by formulas 4
Limit on Logic 8

Top