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

=MID(A1,FIND(" ",A1)-1,1)

Regards

Trevor


"dstiefe" wrote in message
...
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

what if there is more than one blank space - For example

Daniel criag stiefel

Thanks

"Trevor Shuttleworth" wrote:

=MID(A1,FIND(" ",A1)-1,1)

Regards

Trevor


"dstiefe" wrote in message
...
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

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

I want to capture the letter of the space before the empty space and
the letter.


Exactly which letters are you trying to capture? I'm guessing you want the
letter before the right most space and the letter after that same space but
it isn't really clear from the statement above.

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

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

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

"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

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

Try

NumElements = UBound(arrayName) - LBound(arrayName) + 1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"dstiefe" wrote in message
...
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

On Mon, 20 Mar 2006 14:20:29 -0800, dstiefe
wrote:

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.

Well, that's simple. I don't understand what you want to do with the first
letter of the last word, however.

To split off the last word, merely do this

Formula:

With String in A1

B1:
=TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

C1:
=TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

======================
Macro -- altering the selected cell(s) and putting the last word in the
adjacent column:

===============
Sub SplitLastWord()
Dim c As Range
Dim LN As String

For Each c In Selection
LN = Right(c.Text, Len(c.Text) - InStrRev(c.Text, " "))
c.Value = Trim(Left(c.Text, InStrRev(c.Text, " ")))
c.Offset(0, 1).Value = LN
Next c

End Sub
====================




--ron

Below is a subroutine that assumes your original list is in A1:A10. It
places the last names in B1:B10 (one column to the right) and strips the
last name from the original text strings in column A. You will have to
modify for the actual location of the original strings as well as for the
desired destination range for the last names.

Sub BreakNames()
Dim rngA As Range
Dim rngCell As Range
Dim W As Integer
Dim strFirstPart As String

Set rngA = Range("A1:A10")

For Each rngCell In rngA.Cells
If Len(CStr(rngCell.Value)) 0 Then
arrayFullName = Split(rngCell.Value)
rngCell.Offset(0, 1).Value = arrayFullName(UBound(arrayFullName))
strFirstPart = ""
For W = 0 To (UBound(arrayFullName) - 1)
strFirstPart = strFirstPart & arrayFullName(W) & " "
Next W
rngCell.Value = strFirstPart
End If
Next rngCell

End Sub


Steve Yandl

"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