Improvements for text finding functions
 

1. The functions SEARCH(), SEARCHB(), FIND() and FINDB() return a #value
error if they didn't find the "find_text" string within the "within_text"
string. This forces the user to use long formulas such as
=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.
Since in case of success, these functions return the serial number of the
first matching character, starting from 1, I suggest that these functions
return either 0 or -1 if they can't find that text.
2. I suggest to add functions that count the number of times that one text
exists within another, for example:
FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)
FINDNUM is case-sensitive, SEARCHNUM is not.
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

These are already easy enough:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"ma","")))/2

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"ma","")))/2

Substitute being case sensitive.

Biff

"yarp" wrote in message
...
1. The functions SEARCH(), SEARCHB(), FIND() and FINDB() return a #value
error if they didn't find the "find_text" string within the "within_text"
string. This forces the user to use long formulas such as
=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.
Since in case of success, these functions return the serial number of the
first matching character, starting from 1, I suggest that these functions
return either 0 or -1 if they can't find that text.
2. I suggest to add functions that count the number of times that one text
exists within another, for example:
FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)
FINDNUM is case-sensitive, SEARCHNUM is not.
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Biff,
That's very creative!
I'd even generalize your answer a little:
=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)
=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER(A2),"")))/LEN(A2)
I think I may use this in the future, until Microsoft decide there's enough
potential for the functions I've offered to be used.
Thank you for your help!
yarp.

"Biff" wrote:

Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

These are already easy enough:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"ma","")))/2

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"ma","")))/2

Substitute being case sensitive.

Biff

"yarp" wrote in message
...
1. The functions SEARCH(), SEARCHB(), FIND() and FINDB() return a #value
error if they didn't find the "find_text" string within the "within_text"
string. This forces the user to use long formulas such as
=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.
Since in case of success, these functions return the serial number of the
first matching character, starting from 1, I suggest that these functions
return either 0 or -1 if they can't find that text.
2. I suggest to add functions that count the number of times that one text
exists within another, for example:
FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)
FINDNUM is case-sensitive, SEARCHNUM is not.
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc