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,

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,

Thanks for the response.
Column C has FN MI LN data.
Hence, any row of C could be FN MI LN or FN LN or simply FN or simply LN.
My FN MI LN parsing works fine for finding FN or for finding MI or for
finding LN [when there are 3 'words']. My FN LN parsing also works fine for
finding FN or for finding LN [when there are 2 words]. However, if ONLY a
single 'name' is entered, then I get the #value for the MI parsing. I get an
"UnknownFN" for the FN (my default if there's only ONE entry in the Cxx
column) and the LN properly. [If "Joe" is entered I get FN=UnknownFN and
LN=Joe. If "Smith" is entered, I get FN=UnknownFN and LN=Smith. This is
currently acceptable to me.] I just get the #value on the MI which is
unacceptable.

I don't have the FN, MI or LN data in separate columns. They are all in a
single column, column C.
Other ideas for the solution of extracting the MI when ONLY one 'word'
exists in the associated C column?
TIA,
Tom


"K Dales" wrote:

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,

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

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

This formula might do what you want
=IF(ISERROR(SEARCH(" ",SUBSTITUTE(C41,"."," "),SEARCH("
",C41,1)+1)),"UnknownMN",UPPER(MID(C41,SEARCH( " ",C41)+1,1)))

The first part looks for a second space or period changed to space
after the middle initial. If the search fails an error returns
UnknownMN otherwise the uppercase middle initial is returned.

Chris

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,

Chris,
Your solution works great. I've tested it a couple of times and it looks
like it'll do the job.
Ron,
I'll download your dll and test it too! THANKS for each of you for
participating!!
Tom

"Chris Bruce <Fr.... @aol.com" wrote:

This formula might do what you want
=IF(ISERROR(SEARCH(" ",SUBSTITUTE(C41,"."," "),SEARCH("
",C41,1)+1)),"UnknownMN",UPPER(MID(C41,SEARCH( " ",C41)+1,1)))

The first part looks for a second space or period changed to space
after the middle initial. If the search fails an error returns
UnknownMN otherwise the uppercase middle initial is returned.

Chris

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,