Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|