how to trim/change strings in cells

[Desu Nota from Columbus]

I am comparing a list of names of current employees against a training log that dates back 6 years ago. If a name appears in the training log, but not in my current employees list, Column K will show FALSE. If the employee exists in both logs, the value shows TRUE.

I am matching First Name and Last Name in adjacent cells with the following formula:

=SUMPRODUCT(--('Currrent Employees'!$A$1:$A$239=B16),--('Currrent Employees'!$B$1:$B$239=C16))>0

Is there a way to circumvent this problem:

In one list the name is: Mohamed Jones

In the other list: Mohamed A. Jones

The first name is listed as Mohamed A. one place and then in the other Mohamed so the formula doesn't recognize this person.

If there was a way to trim all first names at the first space (so John C. becomes John and Anthony (Tony) becomes Anthony) this problem would be solved.

 

[VoG]

Try this

Excel Workbook
	A	B
1	Mohamed S.	Mohamed
2	Christopher	Christopher
3	Scott	Scott
4	Allen J.	Allen
5	Danny	Danny
6	Michael	Michael
7	Earnest	Earnest
8	Henry Lee	Henry
9	Riccardo J.	Riccardo
10	Mohamad A.	Mohamad
Sheet3

 

[Desu Nota from Columbus]

Perfect, thank you for all your help!