Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Illinois
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by e424932; 2011-03-03 at 12:43.

Posting Permissions

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