Replace #value! error with a blank cell

[enots11]

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

 

[JackDanIce]

Welcome to the board, try:

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

 

[enots11]

Thank you. I tried that and the text

=IFERROR(LEFT(A4,FIND("2a",A4)-1),"") shows up in the cell.

<tbody>
</tbody>

 

[JackDanIce]

Check the formatting settings of the cell, I suspect it's set to Text based on your answer, maybe change it to General

 

[enots11]

I so appreciate this help. We are getting closer....I think. I changed the settings to general and then tried =IFERROR(LEFT(A4,FIND("2a",A4)-1),"")
and now I have a#NAME? error. Is that better? Worse?

 

[enots11]

This worked:

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


Thank you for all of your help.

 

[JackDanIce]

IFERROR is a function in Excel versions 2007 and higher, I suspect you're using 2003, in which case IFERROR would give a #NAME error. Glad you found a solution

 

[enots11]

IFERROR is a function in Excel versions 2007 and higher, I suspect you're using 2003, in which case IFERROR would give a #NAME error. Glad you found a solution

Thank you again. I am using 2010.