Left, Right & If Function

[Darin]

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!

 

[Chip Pearson]

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)

 

[Dave Peterson]

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.