Cut the last character after a space

Topic

New Reply

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

Reply | Quote

Replies

How about:
=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

Reply | Quote

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.

Reply | Quote