Erasing middle initail from Name string

[machielsj]

I've searched and found a similar question, but none of the answer seem to work out for me. There were some slight difference in our set up, but all the same, I appologize if this has already been covered.
I'm attempting to use vlookup to pull data between to workbooks. My problem is, when matching names, one list is populated with no middle initial and the other is mixed. For instance,
in my first work book, Smith John (no comma) is one cell. In the second work book, it may read Smith John C So my thought is, if i can find the second space in the cell(assuming it exists) and delete everything after, I should be good to go. Any thoughts?
Thanks in advance,
James

[Haseeb Avarakkan]

How about using wild card with lookup_value. like

"Smith John*" will work with if there is Smith John on starting. If you use "*Smith John*" this will work if ANYWHERE contains Smith John.

[drfarmkid]

Are you stuck with the second spreadsheet as it is, or can you modify it? If you can modify it, you could use "text to columns" to split the column into three columns, using the space as the delimiter, then use CONCATENATE to put the last and first names back together as desired.

[Jesse11]

I think you dont need to change your data.

If the middle initial is always at the right, maybe INDEX/MATCH works

=INDEX(Sheet2!$B$2:$B$1000,MATCH("*"&A1&"*",Sheet2!$A$2:$a$1000,0))

Where
A1=Smith John

Adjust the ranges and sheets (or workbooks) names

[machielsj]

Text to Column worked like a charm. Many thanks!