2

I need to be able to remove a middle initial if it is present. The naming convention varies and they appear as either:

John Doe, John A Doe, or John A. Doe

I would like all names to be:

John Doe

Here is my current formula, this works in Excel:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,LEFT(A1,SEARCH(" ",A1))&RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(" ",A1)+1)),A1)

1 Answer 1

5

Try to use SEARCH and REPLACE to remove dot as the following

IF(ISERR(SEARCH(".",Title,1)),Title,REPLACE(Title,SEARCH(".",Title,1),1,""))

[OutPut]

enter image description here


To remove Initial Middle, Try this

=IF(ISERR(SEARCH(" ",Title,1)),Title,REPLACE(Title,SEARCH(" ",Title,1),3," "))

[OutPut]

enter image description here

5
  • This only removes the period. I would like both the middle initial and period removed if present or just the middle initial. Aug 9, 2017 at 17:56
  • 1
    Put -1 after the Search() to change the index and change the following character to 2. Use greater #'s if you want to remove extra spaces preceding/following it.
    – Graham
    Aug 9, 2017 at 17:58
  • I wasn't able to get the update formula to work. The formula in my original post works 100% in Excel but does not translate over to SharePoint. Is there any part of my formula that isn't supported? Aug 10, 2017 at 13:00
  • @FlatBanana the excel formula will not work in SharePoint it need alot of modification Meanwhile the above formula in my answer is meet your requirement as shown in the output, so what's the issue now in the above answer!!!!! Aug 10, 2017 at 19:40
  • @M.Qassas I agree with you that it does work, but when there is no middle initial it does not. If the name is John Brown, it comes out as John own. Aug 11, 2017 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.