Left, Right & If Function
 

I have a set of names that all have a different number of letters. Some have
a middle initial (at the end of the word). I'm wondering if there is a way
with a formula that I can cut off the middle intial in the data set. Example:

Sanders, Frank A.
Smith, Tom B.
Jobs, Steve

I would want the formula to recongnize the last period (A.) or the (B.) even
though the words are different lengths. Also there are some people that do
not have middle initials that I'd want to include. Verbally this is what I'm
thinking.

=IF(the name ends in a period(then cut off A. 3 digits to eliminate the
space prior to the middle initial, IF it doesn't end in a period then return
the full name).

Thanks in advance!

Left, Right & If Function
 

Try the following with the name in A1.

=IF(RIGHT(A1,1)=".",LEFT(A1,LEN(A1)-3),A1)


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Darin" wrote in message
...
I have a set of names that all have a different number of letters. Some
have
a middle initial (at the end of the word). I'm wondering if there is a
way
with a formula that I can cut off the middle intial in the data set.
Example:

Sanders, Frank A.
Smith, Tom B.
Jobs, Steve

I would want the formula to recongnize the last period (A.) or the (B.)
even
though the words are different lengths. Also there are some people that
do
not have middle initials that I'd want to include. Verbally this is what
I'm
thinking.

=IF(the name ends in a period(then cut off A. 3 digits to eliminate the
space prior to the middle initial, IF it doesn't end in a period then
return
the full name).

Thanks in advance!

Left, Right & If Function
 

So the middle initial is always preceded by a space and followed by a period?

If yes to both:

=IF(COUNTIF(A1,"* ?.")=0,A1,LEFT(A1,LEN(A1)-3))

* is a wild card that represents any set of characters.
? is a wild card that represents any one character.

So "* ?." is looking in A1 for a bunch of characters followed by a space, then a
single character (any character) followed by a period.



Darin wrote:

I have a set of names that all have a different number of letters. Some have
a middle initial (at the end of the word). I'm wondering if there is a way
with a formula that I can cut off the middle intial in the data set. Example:

Sanders, Frank A.
Smith, Tom B.
Jobs, Steve

I would want the formula to recongnize the last period (A.) or the (B.) even
though the words are different lengths. Also there are some people that do
not have middle initials that I'd want to include. Verbally this is what I'm
thinking.

=IF(the name ends in a period(then cut off A. 3 digits to eliminate the
space prior to the middle initial, IF it doesn't end in a period then return
the full name).

Thanks in advance!

--

Dave Peterson