How to fix a cell format on Excel

Question

How to fix a cell format on Excel

Gerardo Albornoz 20

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.

GA Excel Screenshot

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Marcin Policht 68,535 MVP Volunteer Moderator

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

Marcin Policht 68,535 Reputation points MVP Volunteer Moderator

2025-12-09T22:10:03.87+00:00
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
Gerardo Albornoz 20 Reputation points

2025-12-10T17:00:16.49+00:00

Marcin, thank you for your suggestion; however, it did not work. As you can see on the image below, I have inserted your formula in both P5 and P6 and it did not work on either one.

However, if you look the other image , I have my formula on P7 through P12. you could see that P7 returns a wrong answer becose Excel does not recognized 10 ounces but 1 ounce; however, on P8 Excel recognized 11 ounces fine and returns the correct convertion from lb and oz to oz correctly. Additionally, on cell P9 through P12 Excel is returning the correct answer.

Since this error only occurs when the amount of ounces is 10, or 20, or 30, or 40, or 50 and so on until 90, I am not sure if it has anything to do with the formating in column O so when writing 10.08, it shows as 10 lb . 08 oz. Please see last attached image.

I am sorry for the lengthly replay and thak you for your help.
Gerardo Albornoz 20 Reputation points

2025-12-11T20:54:39.3466667+00:00

Marcin, thank you so much for the new formula it worked just fine with any values. Thank you!
Marcin Policht 68,535 Reputation points MVP Volunteer Moderator

2025-12-11T21:31:22.9866667+00:00

You're welcome. 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

Share via

How to fix a cell format on Excel

0 additional answers

Your answer