How to fix a cell format on Excel

Question

I posted this question last week but the answer I received did not resolve the issue.

When using this function (converts lb and oz to oz):=(TRUNC(O6)*16)+IFERROR(MID(O6,(FIND(".",O6))+1,LEN(O6)),0), for this a cell that reads: 16 lb . 10 oz , the results is 257 oz , but it should be 266 oz. The function(s) is taking the 10 oz as 1 oz; what is wrong, how can I fix this? On column P i am converting

As shown on the added screenshot the formula works fine for other values; the issue is only when the value is xx . 10 oz, or xx . 20 oz, or xx . 30 oz, and so on. These values are 10 oz, 20 oz, and 30 oz but Excel is reconozided them as 1 oz, 2 oz, and 3 oz.

How could I fix this issue? Thank you.

Answer 1

Looks like uour formula is failing because TRUNC(O6) is treating the text “16 lb . 10 oz” as a number, and since the first number Excel finds is 16, everything after that is ignored. The same happens inside the MID function: Excel is extracting the characters after the period but the result is still text like “10 oz”, and you are not removing the “ oz”. When Excel later interprets it as a number, it stops at the first digit and reads “10 oz” as 1, “20 oz” as 2, etc.

The fix would be extracting only the digits of the ounces and then multiply the pounds properly. You can do this by isolating the numbers before “ lb” and before “ oz”.

Use this formula in P6:

=VALUE(LEFT(O6,FIND(" lb",O6)-1))*16 +
VALUE(MID(O6, FIND(".",O6)+2, FIND(" oz",O6)-FIND(".",O6)-2))

This formula reads the pounds as everything before “ lb”, and the ounces as the digits between the period and “ oz”. Excel then converts the text digits to real numbers with VALUE, so “10” stays 10 instead of becoming 1.

If your spacing varies (for example “10 lb. 08 oz” or “10 lb .08 oz”), you can use a more flexible version that extracts only numeric characters:

=VALUE(LEFT(O6,FIND("lb",O6)-1))*16 +
VALUE(TRIM(SUBSTITUTE(MID(O6,FIND(".",O6)+1, FIND("oz",O6)-FIND(".",O6)-1),"oz","")))

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Try the following

=IF(
  ISNUMBER(O6),
  INT(O6)*16 + VALUE(RIGHT(TEXT(O6,"0.00"),2)),
  VALUE(LEFT(O6, FIND(" lb", O6) - 1))*16
    + VALUE(TRIM(MID(O6, FIND(".", O6) + 1, FIND(" oz", O6) - FIND(".", O6) - 1)))
)

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin