Removing salutations/titles with inconsistent data

[Jessseb]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Email[/TD]
[TD]New FN[/TD]
[TD]New LN[/TD]
[/TR]
[TR]
[TD]Laura[/TD]
[TD]Hale, PhD[/TD]
[TD]laurahale@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prof Marcus[/TD]
[TD]Cvija[/TD]
[TD]cvijam@yahoo.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ms Diane[/TD]
[TD]Stuart[/TD]
[TD]diane88@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rey[/TD]
[TD]Torres[/TD]
[TD]rey.torres@yahoo.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]Harris, MBA[/TD]
[TD]sharris@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi All,

I would like to ask for help. I need to delete the salutations for my data found before the name and after the last name. Please view attached information above. I am not sure on how to do it when not all data has salutations. Please help!

Thank you!

Jess

 

[lrobbo314]

First Name

=IF(NOT(ISERR(FIND(" ",A3))),RIGHT(A3,LEN(A3)-FIND(" ",A3)),A3)

Last Name

=IF(NOT(ISERR(FIND(",",B3))),LEFT(B3,FIND(",",B3)-1),B3)

 

[lrobbo314]

Here's another way using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitLast = Table.SplitColumn(Source, "Last Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LastName", "Last Name.2"}),
    SplitFirst = Table.SplitColumn(SplitLast, "First Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"First Name.1", "First Name.2"}),
    First = Table.AddColumn(SplitFirst, "FirstName", each if [First Name.2] = null then [First Name.1] else [First Name.2]),
    Remove = Table.RemoveColumns(First,{"First Name.1", "First Name.2", "Last Name.2"}),
    Reorder = Table.ReorderColumns(Remove,{"FirstName", "LastName", "Email"})
in
    Reorder

 

[Jessseb]

Thank you, it worked!