Removing #VALUE! Cells Based on Number Value of Another Cell

[MDean3313]

Hi all,

I have two columns. One is listed as such:

40/E
32
55
110/E
75/E
95/E
34/E

These numbers are pulled using a formula, and whoever wrote the code for it, wasn't supposed to include the /E. The second column uses the function:

=NUMBERVALUE([@[Wattage (bulb)]])

This pulls anything with a numerical value into that column, however, any cell with the /E makes the 2nd columns cells a #VALUE !

My question is, what can I add to the =NUMBERVALUE([@[Wattage (bulb)]]) formula to ignore the cells with /E and populate the 2nd column with values, instead of errors.

Thanks for the help!

 

[Eric W]

Maybe:

=SUBSTITUTE([@[Wattage (bulb)]],"/E","")+0

 

[tyija1995]

Hey,

If the number is always suffixed with "/E" then try nest your existing function in IFERROR with the following formula if error:

=IFERROR(*your_existing_formula_here*,NUMBERVALUE(LEFT(cell_reference,FIND("/",cell_reference)-1)))