On Mon, 31 Oct 2005 14:01:19 -0500, Ron Rosenfeld
wrote:

On Mon, 31 Oct 2005 06:31:14 -0800, 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,

Here's a solution that I believe will work not only with your examples, but
also with FN MI if MI ends with a period. If MI does not end with a period,
then it will be interpreted as a LN; and I don't know any method, if you just
have a single name, to tell if it is a FN or LN.

In any event, first download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then you can use the following "regular expression" formulas. If there is NMI,
it will return a <blank. If there is just a single name, it will return that
value in the FN, but blanks in MI and LN.

FN: =REGEX.MID($A2,"\w+",1)
MI: =REGEX.MID($A2,"\w+\W",2)
LN: =REGEX.MID($A2,"\s\w+$",1)






--ron

Note:: Substitute the cell where you have the FULLNAME for $A2 in the above
formulas.


--ron