remove #Value! in excel

[hkbhansali]

I have a cell that depends on another formula in another cell if there is no data in the latter cell the former cell displays #VALUE!

Is there a way that I can ensure that that cell the is blank until the data is updated to the depending cell.

The formula that I have is IN C1 =TRIM(MID(A1,FIND("CE",A1)-1,3))

I get the #VALUE! if there is no data in A1

How can I get C1 to remain blank and not VALUE! until A1 receives a value?

[sktneer]

Try this.......

=IFERROR(TRIM(MID(A1,FIND("CE",A1)-1,3)),"")

[mohd9876]

you can use IFERROR as follows: =IFERROR(TRIM(MID(A1,FIND("CE",A1)-1,3)),"") or you can check if A1 is blank and update the formula as follows: =IF(A1="","",TRIM(MID(A1,FIND("CE",A1)-1,3)))

[hkbhansali]

Hi, sktneer,

Thanks for your prompt response,
Thanks for the help.. Great stuff..
its work, thanks again,
hkbhansali