Results 1 to 3 of 3

Thread: Remove the middle initial and period

  1. #1

    Remove the middle initial and period



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to figure out a way to remove the middle initial and period mark. Right now the names are formatted like "Jones, Jack A." but I'd like them formatted like "Jack Jones".

    Jones, Jack A. Jack Jones
    Williams, Bryan K. Bryan Williams

    I have found a few formulas to swap the first name and last name but none that effectively remove the middle initial and the period.

    Any Help would be appreciated!

  2. #2
    =trim(mid(substitute(a1," ",rept(" ",99)),100,99))&" "&left(a1,search(", ",a1,1)-1)

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Rizky's formula won't work on double barrelled surnames. e,g. Van der Walt, Koos J.

    Need something like
    =LEFT(TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))),FIND(" ",TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))))) & " " & TRIM(LEFT(A1,FIND(",",A1)-1))

    1: Split the surname at the comma with =TRIM(LEFT(A1,FIND(",",A1)-1))
    2: Split the names of with TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))
    3: Split the names off on the spaces as in step 1 but replace A1 with step 2
    4: Rejoin in the desired order: A & " " & B

Posting Permissions

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