Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Excel function(S)
I work with alot of names, ID numbers, and text in Excel. Three TEXT
functions I would find handy would be: NameFirst(cell,format) NameLast(cell,format,criteria) Instance(text,criteria) The first two would automatically find the first or last name from a cell containing a name such as Robert H. Smith-Johnson. The format option should allow you to put in a number and get the name returned formatted in lowercase, uppercase, or first letter capitol. NameLast criteria should allow for returning the full last name with or without hyphen, only the first or only the last part of a hyphenated name. The last function would allow users to search for the number of instances of a certain character in a cell. I am sure that others would also find these functions useful instead of writing up long abstract formulas and macros to do what should be a simple tasks. ---------------- 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...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Excel function(S)
I suppose this you help those to lazy to learn the correct use of MID,
LEFT, RIGHT and FIND. By the way in G. Jane Smith - what is the first name G or Jane? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Techrat" wrote in message ... I work with alot of names, ID numbers, and text in Excel. Three TEXT functions I would find handy would be: NameFirst(cell,format) NameLast(cell,format,criteria) Instance(text,criteria) The first two would automatically find the first or last name from a cell containing a name such as Robert H. Smith-Johnson. The format option should allow you to put in a number and get the name returned formatted in lowercase, uppercase, or first letter capitol. NameLast criteria should allow for returning the full last name with or without hyphen, only the first or only the last part of a hyphenated name. The last function would allow users to search for the number of instances of a certain character in a cell. I am sure that others would also find these functions useful instead of writing up long abstract formulas and macros to do what should be a simple tasks. ---------------- 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...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Excel function(S)
Techrat wrote...
I work with alot of names, ID numbers, and text in Excel. Three TEXT functions I would find handy would be: NameFirst(cell,format) NameLast(cell,format,criteria) Instance(text,criteria) .... Depends on culture. What are first and last names for Mao Zedong, Aung San Suu Kyi, Kiri te Kanawa, Oscar de la Hoya, Carlos Salinas de Gortari? Last names aren't just hyphenated, and 'last' name isn't always surname. And continuing with hispanic names, would Martin be a middle name or surname in Javier Martin Reyes? If there were a single, simple, general search function, that along with MID, LOWER, PROPER and UPPER would be all that's needed. The search function could look like FIND and SEARCH, but it should take instance arguments rather than starting position arguments, and negative instance arguments would mean search from end to beginning. For example, if x were "The quick brown fox jumps over the lazy dog.", then =NEWFUNC(" ",x) would return 4 (just like FIND) =NEWFUNC(" ",x,3) would return 16 =NEWFUNC(" ",x,-2) would return 35 First would would be given by =MID(x,1,NEWFUNC(" ",x)-1) Last word given by =MID(x,NEWFUNC(" ",x,-1)+1,32) or =REPLACE(x,1,NEWFUNC(" ",x,-1),"") all words after the second word by =REPLACE(x,1,NEWFUNC(" ",x,2),"") and FTHOI the 4th word by =MID(x,NEWFUNC(" ",x,3)+1,NEWFUNC(" ",x,4)-NEWFUNC(" ",x,3)-1) Or you could download and install Laurent Longre's MOREFUNC.XLL add-in and use its WMID, WORDCOUNT and MCONCAT functions. There are some programming languages that get by with only equivalents for LEN, MID and FIND. Excel already has several hundred functions, and most users use fewer than 100 of them. If more functions are needed (and more are), they should be general ones, not overly specialized ones. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Functions in Excel | Excel Worksheet Functions | |||
How do I see the formula calculation used in Excel functions box | Excel Worksheet Functions | |||
Improved Excel functions curve fitting in sheet | Excel Worksheet Functions | |||
Are there functions that perform robust statistics in Excel? | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |