DPIC44: Definitely advanced for your level, eh? Well by the time we've got this thing wrapped up tight, your level will have been kicked up a notch! Let me slip into lecture mode on Data Quality for a moment.

Data Quality: To assume or not to assume (with apologies to Shakespeare).
We got involved with this thing without knowing anything about what activity preceded the current problem. Much of the logic we're applying depends on the data not having any extra spaces lying around. The three test-data names don't seem to have any problem (although I'm sure I'm right about Ms. Harris messing things up)*, so we make assumptions that allow us to take certain shortcuts. The fact is, test data isn't live data. This is really a Systems Analysis (SA) concern, not a programming concern. A good SA will get the data verified for quality as early in the process as possible, so that every program that follows can legitimately take shortcuts that depend on that quality, saving a lot of programming, coding, and cpu time. Would you trust the data's quality if it were the creation of keyboard input? So keep that in mind for the current problem. One single TRIM statement will guarantee that there are no multiple spaces within the name and no leading or trailing spaces; and by the way, your test data should include some bad stuff just to make sure it gets handled properly. You can throw out the TRIM statement later if your SA tells you that data quality is guaranteed.

Thread discipline: I screwed up right at the start because the spreadsheet formulas differed from those in the post and I was studying the spreadsheet version. I later got an apology for this. Now we've got a slew of spreadsheets with different titles, each one presumably an improvement with Left-Mid-Right_3-1.xlsx representing a helluvalot of work, but I'm wondering what I missed in Left-Mid-Right_3, and why the switch in numbering? I'm not sure which spreadsheet I should be looking at.

(Time passes)

I think I've finally realized that you're working with the highlighted area that deals with the ISERROR function. I'm too tired to study it now, but at first glance I'd prefer to stay with the IFERROR technique. It's simple and compact and works, once we pre-trim to handle the Harris-type problem. Admittedly, its mechanism is a bit weird, but once you get the feel of it, it does the job quite nicely. I like it.

Oh m'Gawd, color me red. I just found out that ...3-1, and ...3-2, etc are being caused by my own downloading after not being able to get Excel to show me ...3. I'm going to bed now. I'll look at it tomorrow.

*I WAS sure, but see the next post!