This VBA function below could be placed in a module and then
=LastName(a1)
would pull the last name from A1 etc..

However, you also say that you only want the first letter of the last word
which isn't quite the same but the routine could easlily be modified if that
is what you're looking for. If you really want to erase the last name from
the original column, you will need a subroutine but I'm not sure if you want
to be left with a column that has entries like Mr and Mrs. with nothing
else.

Anyway, here is the function:

Function LastName(rng As Range) As String
If Not Len(CStr(rng.Value)) 0 Then
Exit Function
Else
arrayName = Split(rng.Value)
LastName = arrayName(UBound(arrayName))
End If
End Function

Steve


"dstiefe" wrote in message
...
I want the first letter of the last word...basically I have a list:

Mr & Mrs. Dan
Mr & Mrs. Mike
Dr & Mrs. nameing

I want to take the last word (i.e. dan, mike, and nameing) and put it into
a
new colum and erase it from the old.

but I was having a problem looping through the individual characters
within
the cell.

does that make more sense? Sorry for the lack of clarity

Thanks for your help

"Ron Rosenfeld" wrote:

On Mon, 20 Mar 2006 13:00:33 -0800, dstiefe

wrote:

I have a column with rows of names for example:

danil stiefel
jae lowed
dist help

I want to count, starting from the right, and when I reach the first
blank
space I want to capture the letter of the space before the empty space
and
the letter.

Thank you

Not quite sure what you are asking.

Do you want the first letter of the last word or the last letter of the
next to
last word?

Will there always be at least two words?

First letter last word:

=MID(A10,FIND(CHAR(1),SUBSTITUTE(
A10," ",CHAR(1),LEN(A10)-LEN(
SUBSTITUTE(A10," ",""))))+1,1)

Last letter next to last word:

=MID(A10,FIND(CHAR(1),SUBSTITUTE(
A10," ",CHAR(1),LEN(A10)-LEN(
SUBSTITUTE(A10," ",""))))-1,1)

If there might be only one word, the formulas will return a #VALUE!
error.


--ron