Showoff !<g

Very impressive though.<g
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Ken Wright" wrote in message
...
Each cell/word has a diffent number of charachers so i
cant use the MID function

Remember, MID expects you to specify where to start pulling the string you
want from the string you have, so think about it - How do you tell it to
find the last space in the string and then start from the next character.
FIND() is good because it will find a space in your string for you, but the
problem is you have more than one space, so what you can do is somehow
switch the very last space for a character that is likely to be unique to
your string, eg something like [. A good function to do this would perhaps
be SUBSTITUTE which works similar to FIND but allows you to replace a
specific instance of a character. Thing is you then need to know how many
instances of that character there are in the string, so maybe substitute all
instances of a space with nothing, and then look at the length of the string
before and after.

So from that assumption, and working with 'your_string' in A1 (I'll assume
its 'abc def ghijk'), lets first see how many spaces are in your string:-

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) < This gives 2 in my example string


OK so far, so now lets switch the 2nd instance of a space (Which is the
character just before the name you want to get) for a unique character such
as [

=SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) < This now
gives me 'abc def[ghijk'

So now we need to find where that character [ actually is so that we can use
that for the MID function, so:-

=FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) <
This gives me 8

So now I know that the last space in the original string is sat in the 8th
position, and i know that the data i want starts from that plus one, ie the
9th, so....

=MID(A1,FIND("[",SUBSTITUTE(A1," ","[",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,LEN(A1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Joe" wrote in message
...
I have three words per cell, and a whole list of cell.
I want to pick up the last word in the cell only and put
it in another cell.
Each cell/word has a diffent number of charachers so i
cant use the MID function
does anybody have any ideas
Thanks