New Excel function(S)

[Techrat]

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/com...dg=microsoft.public.excel.worksheet.functions

 

[Bernard Liengme]

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?

 

[Harlan Grove]

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.