• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace a #VALUE! error with a blank cell

E

enots11

New Member
I am working on an observation form where an evaluator with type in text from the observation in the first column and then code it. We have 8 codes 2a,2b,2c,2d,3a,3b,3c,3d. I then wanted all the 2a information to move to a column names 2a, all the 2b information to a column named 2b and so on.


In column A the evaluator would type in information such as teacher greeted students when they entered the room, 2a


the ,2a would be the coding that refers to our rubric.


I used =LEFT(A4,FIND("2a",A4)-1)


All is well and the information is going where it is suppose to, but in the other 7 columns there is an error message #value! that I do not want. I have done a conditional formatting to make the text white but when I copy and paste the information into another file the error message shows.


Any idea how I can just get rid of the error message in the other cells?


TIA

Donna
 
If you have 2007+

=IFERROR(LEFT(A4,FIND("2a",A4)-1),"")


Otherwise:

=IF(ISERROR(LEFT(A4,FIND("2a",A4)-1)),"",LEFT(A4,FIND("2a",A4)-1))
 
Luke,


You are a genius. I have 2010. I think I am going to cry. No crying on tech-sites!

Thank you so much.

Donna
 
You must log in or register to reply here.
Back
Top