I assume then your first and last name parsing works fine for all cases? If
that is so, it should be easy to find the middle initial, if any. Assuming
last name is in D41 and first name in E41, to get middle initial:
=TRIM(SUBSTITUTE(SUBSTITUTE(C41,D41,""),E41,""))
--
- K Dales


"Tom" wrote:

I have a set of data column C that is typically
1) FN MI LN or
2) FN LN or
3) FN
where FN = firstname, MI = middle initial and LN= last name.

I've created the proper parsing for the "FN MI LN" and for "FN LN".
However, when I have data in a column that is FN only (or even the LN only
for that matter), I get a #value error in my MI column. That is, a SINGLE
entry in Column C generates the #value error message.

Here's what I have and I'm unable to find where the #value is coming from
and how to correct it.
Is there an easy way to do this rather than just 'looking at the code'?
Like, can I step through the function to see where the error is occurring?
Or even trace the process with resultant 'true/false' or value determinations?

If someone has a solution, GREAT, that would highly appreciated.
So, here's the MI code that generates the #value error message when the
column only contains FN or LN:
=IF(UPPER(C41)="","",IF(MID(C41,FIND(" ",C41,1)+2,1)=".",MID(C41,FIND("
",C41,1)+1,1),IF(MID(C41,FIND(" ",C41,1)+2,1)=" ",MID(C41,FIND("
",C41,1)+1,1)," ")))

If I can't find a good solution, I'll probably save the *.xls into an *.csv
and Replace the #value to blanks.
TIA,