software

eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
suppose that these r the names and i want formula that would be generic to
all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;

One way:

=TRIM(MID(LEFT(A1, FIND(quot;$quot;, SUBSTITUTE(A1, quot;_quot;, quot;$quot;, 2))),
FIND(quot;_quot;, A1), 32767))

where _ represents a space character to prevent unfortunate linewrap.

In article gt;,
quot;zomexquot; gt; wrote:

gt; eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
gt; suppose that these r the names and i want formula that would be generic to
gt; all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;

Hi Zomex

your teacher is obviously in a mean mood today

for this one - one answer involves a combination of MID and SEARCH functions

the way to approach it is
- locate the first space using the SEARCH function
- locate the second space using the SEARCH function (you'll have to nest the
first formula in here again to get your starting point), then subtract the
first formula again to calculate the number of chatacters between the two
spaces.

then nest these two formulas in the MID function.

Using the above method will give you leading and trailing spaces, these can
easily be edited out by modifying the formula (once you get that far).

Give it a go and if you'ld like more help, just post back.

Cheers
JulieD

julied at hctsReMoVeThIs dot net dot auquot;zomexquot; wrote:

gt; eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
gt; suppose that these r the names and i want formula that would be generic to
gt; all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;

thanks for the wonderful reply.... i couldnt get the middle name though the
formula that i was using sure did get me the last name and the3 formula is
=MID(E4,FIND(quot; quot;,E4,FIND(quot; quot;,E4) 1),LEN(E4)) ...the other formula did
work, but it shows the result like quot;0UsManquot; and for even it shows
quot;1UmErquot;...how can i remove this quot;0quot; abd quot;1quot;...

quot;JulieDquot; wrote:

gt; Hi Zomex
gt;
gt; your teacher is obviously in a mean mood today
gt;
gt; for this one - one answer involves a combination of MID and SEARCH functions
gt;
gt; the way to approach it is
gt; - locate the first space using the SEARCH function
gt; - locate the second space using the SEARCH function (you'll have to nest the
gt; first formula in here again to get your starting point), then subtract the
gt; first formula again to calculate the number of chatacters between the two
gt; spaces.
gt;
gt; then nest these two formulas in the MID function.
gt;
gt; Using the above method will give you leading and trailing spaces, these can
gt; easily be edited out by modifying the formula (once you get that far).
gt;
gt; Give it a go and if you'ld like more help, just post back.
gt;
gt; Cheers
gt; JulieD
gt;
gt; julied at hctsReMoVeThIs dot net dot au
gt;
gt;
gt; quot;zomexquot; wrote:
gt;
gt; gt; eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
gt; gt; suppose that these r the names and i want formula that would be generic to
gt; gt; all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;

Maybe

=MID(E4,FIND(quot; quot;,E4,FIND(quot; quot;,E4) 1) 1,LEN(E4))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;zomexquot; gt; wrote in message
...
gt; thanks for the wonderful reply.... i couldnt get the middle name though
the
gt; formula that i was using sure did get me the last name and the3 formula is
gt; =MID(E4,FIND(quot; quot;,E4,FIND(quot; quot;,E4) 1),LEN(E4)) ...the other formula
did
gt; work, but it shows the result like quot;0UsManquot; and for even it shows
gt; quot;1UmErquot;...how can i remove this quot;0quot; abd quot;1quot;...
gt;
gt; quot;JulieDquot; wrote:
gt;
gt; gt; Hi Zomex
gt; gt;
gt; gt; your teacher is obviously in a mean mood today
gt; gt;
gt; gt; for this one - one answer involves a combination of MID and SEARCH
functions
gt; gt;
gt; gt; the way to approach it is
gt; gt; - locate the first space using the SEARCH function
gt; gt; - locate the second space using the SEARCH function (you'll have to nest
the
gt; gt; first formula in here again to get your starting point), then subtract
the
gt; gt; first formula again to calculate the number of chatacters between the
two
gt; gt; spaces.
gt; gt;
gt; gt; then nest these two formulas in the MID function.
gt; gt;
gt; gt; Using the above method will give you leading and trailing spaces, these
can
gt; gt; easily be edited out by modifying the formula (once you get that far).
gt; gt;
gt; gt; Give it a go and if you'ld like more help, just post back.
gt; gt;
gt; gt; Cheers
gt; gt; JulieD
gt; gt;
gt; gt; julied at hctsReMoVeThIs dot net dot au
gt; gt;
gt; gt;
gt; gt; quot;zomexquot; wrote:
gt; gt;
gt; gt; gt; eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
gt; gt; gt; suppose that these r the names and i want formula that would be
generic to
gt; gt; gt; all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;
On Tue, 3 Jan 2006 21:19:02 -0800, quot;zomexquot; gt;
wrote:

gt;eg. quot;mick jagger bushquot; quot;muhammad ali clayquot; quot;ayesha bin batoolquot;
gt;suppose that these r the names and i want formula that would be generic to
gt;all, such that it gets quot;jaggerquot; quot;aliquot; and quot;binquot;=LEFT(REPLACE(A1,1,FIND(quot; quot;,A1),quot;quot;),FIND(quot; quot;,REPLACE(A1,1,FIND(quot; quot;,A1),quot;quot;))-1)

Will find the first word after the first space.

If there may be multiple spaces within the string, then replace all A1 with
TRIM(A1)

On the other hand, a little more complex, imagine that you may have name
strings with 2, 3, or more names.

You might want to extract the first name, last name, and all of the middle
names separately. You also might want to be able to strip off Titles and such
(e.g. Mr., Ms., Jr, III, Sr.)

If this is a possibility, then regular expressions would be a more powerful
tool to use. You can look at that by installing Longre's free morefunc.xll
add-in from xcell05.free.fr

First Name:=REGEX.MID(TRIM(A1),quot;\w quot;)
Last Name:=REGEX.MID(TRIM(A1),quot;\w $quot;)
Middle Names:=REGEX.MID(TRIM(A1),quot;(?lt;=\s)(\w \s) quot;)

Note that the formula for Middle Names will return a blank if there are only
two names (i.e. no middle name).--ron