Thread: Cut the last character after a space

1. Cut the last character after a space

I need to get rid of the middle initial if they have one. It's always at the end after a space. example John Smith A.
in Excel 2000
Thanks so much

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2, LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),2))-1),A1)

If A1 has 2 spaces in it (so of the form Last - space-first-space-Middle), it will give Last-space-first
If A1 is does NOT have 2 spaces in it, it will just give the original A1.

Steve

3. Try this:

=IF(ISERROR(FIND(" ",A1,LEN(A1)-1)),A1,LEFT(A1,LEN(A1)-2))

It looks to see if the second to last character is a space. If yes (they have a middle initial), then it shows the first LEN-2 characters. If no (no middle initial), then it shows the entry as originally entered.

Steve's formula would not work for people who have a space in their last name or who have a suffix like Jr or Sr, such as Joseph De Jonge, Joseph De Jonge A, Buck Shot Jr, or Buck Shot Jr A.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•