thank you for the detailed description....question....how would I figure out
how many subsets the "split" has created?

Thanks

"Steve Yandl" wrote:

"Split" takes a text string and turns it into an array of text values, in
other words, it breaks it into a set of smaller text strings. You can name
a delimter (the character that establishes where to break the original
string apart) but if one isn't named it is assumed to be the space character
which is what we want in this case. I had split create an array named
"arrayName" from whatever string it is fed. The first element of the array
would be arrayName(0), if there is a second element it would be
arrayName(1), the next element arrayName(2), etc. UBound is the numerical
reference for the last or final element, so arrayName(UBound(arrayName)
returns the text string that represents the last text string in the set of
strings created from the original string, which in this case is the last
name.

Steve



"dstiefe" wrote in message
...
what does the "split" from Split(rng.Value) doing? in laymen please

thanks for all of your help

I'm getting it to copy to another column...how do I get the last name to
be
deleted from the original column...so I only have the Mr. & Mrs...?



"Steve Yandl" wrote:

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