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

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,A1,LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)))

 

[Andrew Poulsom]

Maybe you can use:

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

 

[Desu Nota from Columbus]

VOG- I couldn't get your formula to work. i don't understand the syntax

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,A1,LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)))

changed to:

=IF(LEN(B16)-LEN(SUBSTITUTE(B16," ",""))=1,B16,LEFT(B16,FIND(" ",B16))&TRIM(RIGHT(SUBSTITUTE(B16," ",REPT(" ",255)),255)))

Couldn't get this to mesh with my SUMPRODUCT Formula. How would you accomplish this?

Andrew- Your formula fixed 50% of the errors, for instance Jeffrey --> Jeff are now recognized, but it still doesn't recognize my examples given above.

 

[Desu Nota from Columbus]

Would it be simpler to achieve this by macro?

Trim the names on Sheet("SDIC") in the range B16:B331 as described above, and then do the comparison?

 

[VoG]

It is probably simplest to use a helper column like this

Excel Workbook
	A	B
1	Mohamed Jones	Mohamed Jones
2	Mohamed A. Jones	Mohamed Jones
3	Fred T Scuttle	Fred Scuttle
4	Fred Scuttle	Fred Scuttle
Sheet1

 

[Desu Nota from Columbus]

I just tried this formula and it is not doing what I see in your post.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,A1,LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)))

I put all the first names in column A, and then copy/pasted this formula to B1 and dragged it down.

Names like Gary, Terrie, Trent, Caprice, James... return a #Value!

And names like John Q., and Michael Lee return John Q. and Michael Lee, respectively.

I am thoroughly confused now

 

[VoG]

Sorry I think that I completely misunderstood - I thought that each name was in a single cell.

 

[Desu Nota from Columbus]

Here are some samples of what I am getting:

<table width="190" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3474; width:71pt" span="2" width="95"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:71pt" height="17" width="95">Mohamed S.</td> <td style="width:71pt" width="95">Mohamed S.</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Christopher</td> <td align="center">#VALUE!</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Scott</td> <td align="center">#VALUE!</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Allen J.</td> <td>Allen J.</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Danny</td> <td align="center">#VALUE!</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Michael</td> <td align="center">#VALUE!</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Earnest</td> <td align="center">#VALUE!</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Henry Lee</td> <td>Henry Lee</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Riccardo J.</td> <td>Riccardo J.</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">Mohamad A.</td> <td>Mohamad A.</td> </tr> </tbody></table>

 

[Desu Nota from Columbus]

Sorry I think that I completely misunderstood - I thought that each name was in a single cell.

That explains it. First Name and Last Name are in adjacent columns.