#Value
 

EXcel 2000 on Win2000

Have to correct a column of names, some enter lname, fname others entered as
fname lname. Following formula correctly places those lname, fname entries(in
column D), however it returns that nasty #Value error for those that are
formatted correctly( inother words do not have the comma separating lname
from fname). My intentions were to simply copy the contents of the D cell, if
there were no ",".

What would be the best way to work around this frustration??

=IF(SEARCH(",", D2)1,RIGHT(D2, LEN(D2)-(SEARCH(",", D2)+1))&" "&LEFT(D2,
(SEARCH(",", D2)-1)), D2)

Thanks for any and all pointers

#Value
 

One way:
=IF(ISERROR(SEARCH(",",D2)),D2,RIGHT(D2,LEN(D2)-(SEARCH(",",D2)+1))&"
"&LEFT(D2,(SEARCH(",",D2)-1)))

HTH,
Paul

"JR Hester" wrote in message
...
EXcel 2000 on Win2000

Have to correct a column of names, some enter lname, fname others entered
as
fname lname. Following formula correctly places those lname, fname
entries(in
column D), however it returns that nasty #Value error for those that are
formatted correctly( inother words do not have the comma separating lname
from fname). My intentions were to simply copy the contents of the D cell,
if
there were no ",".

What would be the best way to work around this frustration??

=IF(SEARCH(",", D2)1,RIGHT(D2, LEN(D2)-(SEARCH(",", D2)+1))&" "&LEFT(D2,
(SEARCH(",", D2)-1)), D2)

Thanks for any and all pointers

#Value
 

That certainly solved the #Value error for those entries formatted as fname
space lname. However for some on those that needed to be to be converted, it
added a small square between the fname and lname.


**** Oops, found my error. I simply copied your formula and pasted directly
into the spreadsheet. I noticed that once copied into the cell, the line
broke in the middle of the inserted space(" ") Evidently Excel interpreted
that as a graphic character. Once I deleted the invisible characters between
the quotes and re-entered a single space, it worked fantastically.

Thanks for that introduction to the iserror function.

"PCLIVE" wrote:

One way:
=IF(ISERROR(SEARCH(",",D2)),D2,RIGHT(D2,LEN(D2)-(SEARCH(",",D2)+1))&"
"&LEFT(D2,(SEARCH(",",D2)-1)))

HTH,
Paul

"JR Hester" wrote in message
...
EXcel 2000 on Win2000

Have to correct a column of names, some enter lname, fname others entered
as
fname lname. Following formula correctly places those lname, fname
entries(in
column D), however it returns that nasty #Value error for those that are
formatted correctly( inother words do not have the comma separating lname
from fname). My intentions were to simply copy the contents of the D cell,
if
there were no ",".

What would be the best way to work around this frustration??

=IF(SEARCH(",", D2)1,RIGHT(D2, LEN(D2)-(SEARCH(",", D2)+1))&" "&LEFT(D2,
(SEARCH(",", D2)-1)), D2)

Thanks for any and all pointers

#Value
 

No problem.
Thanks for the feedback.

"JR Hester" wrote in message
...
That certainly solved the #Value error for those entries formatted as
fname
space lname. However for some on those that needed to be to be converted,
it
added a small square between the fname and lname.


**** Oops, found my error. I simply copied your formula and pasted
directly
into the spreadsheet. I noticed that once copied into the cell, the line
broke in the middle of the inserted space(" ") Evidently Excel
interpreted
that as a graphic character. Once I deleted the invisible characters
between
the quotes and re-entered a single space, it worked fantastically.

Thanks for that introduction to the iserror function.

"PCLIVE" wrote:

One way:
=IF(ISERROR(SEARCH(",",D2)),D2,RIGHT(D2,LEN(D2)-(SEARCH(",",D2)+1))&"
"&LEFT(D2,(SEARCH(",",D2)-1)))

HTH,
Paul

"JR Hester" wrote in message
...
EXcel 2000 on Win2000

Have to correct a column of names, some enter lname, fname others
entered
as
fname lname. Following formula correctly places those lname, fname
entries(in
column D), however it returns that nasty #Value error for those that
are
formatted correctly( inother words do not have the comma separating
lname
from fname). My intentions were to simply copy the contents of the D
cell,
if
there were no ",".

What would be the best way to work around this frustration??

=IF(SEARCH(",", D2)1,RIGHT(D2, LEN(D2)-(SEARCH(",", D2)+1))&"
"&LEFT(D2,
(SEARCH(",", D2)-1)), D2)

Thanks for any and all pointers