Removing middle initial from end of first name

[Hayley]

Hi everyone,
Is there a way to remove a middle initial from the end of a first name? I have data where the first name fields could be Nathanial J and then the last name field is Smith. Sometimes there's no middle initial there. Sometimes the first name is 2 names (e.g. Mary Anne) so I can't remove everything after the first space.

Hoping someone has an easy formula I can use to get rid of these.

Thanks,

 

[salim hasan]

Try this UDF (User defined Function)

Option Explicit
Function Salim_Split_Name(N_name, n)
Rem  ====>> Created By Salim Hasan On 13/8/2019
Dim x%
Dim my_name
Dim My_Col As New Collection
 
  my_name = Split(N_name)
   For x = LBound(my_name) To UBound(my_name)
        My_Col.Add my_name(x)
   Next x
    If n <= My_Col.Count Then
        Salim_Split_Name = My_Col(n)
    Else
        Salim_Split_Name = ""
    End If
     Set My_Col = Nothing
  End Function

Attached file is an Exemple

 

[Peter Bartholomew]

A formula:

= IFERROR(
IF( ( 1 + SEARCH( " ", TRIM(firstName) ) ) = LEN(TRIM(firstName)),
LEFT(TRIM(firstName), LEN(TRIM(firstName)) - 2 ),
TRIM(firstName) ),
TRIM(firstName) )

The error trap caters for no space to be found or a name that comprises only of a single initial. The formula will not remove multiple trailing initials.


I tend to use named formulas to hold the inner parts of a nested formula so I would have
= IFERROR(
IF( hasTrailingInitial?, setName, trimmedName ),
trimmedName )
Since others do not do this, use the first version.

 

[Hayley]

A formula:

= IFERROR(
IF( ( 1 + SEARCH( " ", TRIM(firstName) ) ) = LEN(TRIM(firstName)),
LEFT(TRIM(firstName), LEN(TRIM(firstName)) - 2 ),
TRIM(firstName) ),
TRIM(firstName) )

The error trap caters for no space to be found or a name that comprises only of a single initial. The formula will not remove multiple trailing initials.


I tend to use named formulas to hold the inner parts of a nested formula so I would have
= IFERROR(
IF( hasTrailingInitial?, setName, trimmedName ),
trimmedName )
Since others do not do this, use the first version.

This worked (the first one as you suggested). I don't understand what it's doing but I retyped it exactly as you had it and by gum, it worked! Thank you!

 

[Peter Bartholomew]

@Hayley
If you want a description:
1. TRIM simply removes spurious spaces from the name;
2. SEARCH finds the first remaining space and returns its location;
3. IF moving on 1 character gets you to the end of the string, the name has a middle initial;
4. LEFT returns all the characters except the final two (the middle initial and the preceding space;
5. IF the end of the string is not reached assume you have a middle name and return the original string;
6. IFERROR will come into play if there is no space to find so the original name is to be returned.