Fix #VALUE! in Google Sheets (wrong type)

#VALUE! means the formula parsed, then hit a value of the wrong type. Sheets expected a number (or a date) and got text it cannot use. The function name is fine. The cell is not a lookup miss.

Recreate it on a blank sheet

1. Open a blank spreadsheet. In A1 type qty, B1 unit, C1 add, D1 ok.
2. In A2 type 10 as a number. In B2 type pcs as plain text. In C2 enter =A2+B2. The cell shows #VALUE!. The tooltip is Function ADD parameter 2 expects number values. But ‘pcs’ is a text and cannot be coerced to a number.
3. In A3 type 10. In B3 type 2 as a number. In D3 enter =A3+B3. The cell shows 12. Same operator, numeric cells, no error.

Leave C2 as the broken formula.

Second recreate — a function that wants a number. In E1 type value_fn. In E2 enter =VALUE("pcs"). The cell shows #VALUE!. VALUE only converts a string that already looks like a number, a date, or a time. pcs is none of those.

Third recreate — a function that wants a date. In F1 type datevalue. In F2 enter =DATEVALUE("Widget"). The cell shows #VALUE!. Widget is not a date string.

A text number is a different case. In G1 type text_num. In G2 type an apostrophe then 10 so the cell is text. In G3 enter =G2+1. The cell shows 11. Arithmetic will coerce '10. It will not coerce pcs. That is why C2 fails and G3 does not.

What is actually wrong

• +, -, *, and / need numbers. B2 is the word pcs. Sheets will not add 10 and a unit label.
• VALUE and DATEVALUE fail the same way when the string is not numeric or not a date. The argument is text. It is the wrong kind of text.
• SUM(A2:B2) on this sheet is 10, not #VALUE!. SUM skips text. The + operator does not. If you expected a total of qty plus something in B, + is the operator that surfaces the type mix.
• A function argument can be the wrong type even when both cells look “filled in.” MID("Widget",0,3) is #VALUE! because the start position must be 1 or greater. The text is fine. Argument 2 is not.

This is not #N/A. #N/A is a lookup or filter miss. The formula ran and found no match. See what #N/A means. Nothing is being looked up here.

This is not #NAME?. #NAME? is an unknown function or name (VLOKUP). See unknown function. VALUE and DATEVALUE are spelled correctly. They rejected the argument.

This is not a Formula parse error. A parse error is #ERROR! from commas vs semicolons on the spreadsheet locale. See locale parse error. C2 parsed. Then ADD refused pcs.

This is not #NUM!. #NUM! is a number the function cannot use in its domain, such as SQRT of a negative. The type was already numeric. See number out of domain. pcs never got that far.

A related #VALUE! that is not this recreate: VALUE(TRIM(...)) on a sku that still holds CHAR(160). TRIM does not strip that character, so VALUE still fails. See CHAR(160) blanks.

Fix

Leave C2, E2, and F2 broken so the error stays on the sheet.

Stop adding the unit. Qty is A2. The unit label belongs in a header or a separate text column. In H1 type qty_only. In H2 enter =A2. The cell shows 10. If you need a label for display, concatenate after the math: =A2&" pcs" returns 10 pcs as text. Do not put pcs inside the addition.

Convert only when the text is a number. In G3 you already have 11 from '10+1. For a text number in its own cell, =VALUE(G2) returns 10. =VALUE("pcs") will not. Do not wrap every #VALUE! in VALUE and hope.

Pull digits out of a mixed string. If B2 were 10 pcs and you truly need the 10, isolate the number first, then convert. In I1 type extract. In I2 enter =VALUE(REGEXEXTRACT("10 pcs","[0-9]+")). The cell shows 10. That pattern is for a string that contains digits. It is not a fix for the word pcs alone.

Guard the type. When a column is allowed to hold text (n/a, pending, a unit), do not add it. In J1 type if_number. In J2 enter =IF(ISNUMBER(B2),A2+B2,""). The cell is empty. Copy the idea onto real rows: ISNUMBER is the test. IFERROR would also hide #REF! and #DIV/0! if the formula later breaks in a different way. Prefer the type check.

Fix the input. If B2 was supposed to be 2 and someone typed pcs, type the number. C2 becomes a number and you do not need a wrapper.

Do not wrap a type error you have not looked at. If B2 should have been numeric and is not, fix B2. IFERROR will not.