Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
Default User Defined Functions - How? | Excel Discussion (Misc queries) | |||
PASTE DOWN FUNCTIONS | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions |