Most Efficient Way to Resort Strings

Hello all, Excel newb here.

My task is to sync some date between two massive spreadsheets via VLOOKUP.
Spreadsheet one is contact info, Spreadsheet two is contact info plus ID numbers.
Some people from spreadsheet one correspond to spreadsheet two. The plan is to use VLOOKUP to immediately link the corresponding parties with their ID numbers.

The problem is, to use VLOOKUP, the data needs to match exactly. My names from spreadsheet one do not include middle names, and some of the names from spreadsheet two do.

So, on spreadsheet two, I used text column split to separate the names into columns, and concatenate to rearrange them, absent the middle name in a third column. I am then going to use the third column for VLOOKUP.
However, if the name already does not have a middle name, my concatenate result just gives me the first name, because the function gives a blank result.

This is easily solvable, I created a second concatenate formula that simply transfers the name to the third column.

However, to go through the entire 25k entry sheet to copy in the second formula when applicable would take a couple of hours.

If was wondering if anyone knew of perhaps a "smarter" Excel function, or perhaps a script that could apply the appropriate function where it is needed? Like, some kind of "If x, use formula one, if y, use formula two" sort of thing.

Thanks.

Please Login or Register  to view this content.

Nest the two different concatenation formulas inside some IFs to get what you want.