+ Reply to Thread
Results 1 to 11 of 11

Converting text weights to ounces

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Converting text weights to ounces

    I have a long list of text values like "8lbs 9oz" that I need to compare to a standard weight. Seems to me the easiest method would be to convert pounds to ounces to express the whole value in ounces and then do the calculation, but I'm getting tripped up in how to convert to ounces. I've attached a workbook with the correct answers, need help in figuring out a formula to get those correct answers. Any help would be appreciated.
    Attached Files Attached Files
    I know I'm not stupid, but I suspect I'm a lot stupider than I think I am

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Converting text weights to ounces

    C2 =$D$2-(LEFT(B5,SEARCH("lbs",B5,1)-1)*$H$2+ABS(IFERROR(MID(B5,SEARCH(" ",B5),(SEARCH($I$1,B5)-SEARCH(" ",B5)))*$I$2,0)))

    See the attached file.

    I notice I have differant answers in C10 and C13.
    Last edited by oeldere; 05-26-2022 at 10:45 AM. Reason: changed dutch formula in English
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Converting text weights to ounces

    ...another option

    C3: =LEFT(SUBSTITUTE(C2,"lbs"," "),2)*16+IF(LEN(C2)<6,0,SUBSTITUTE(RIGHT(C2,4),"oz",""))

    C5 copied down: =C$3-(LEFT(SUBSTITUTE(B5,"lbs"," "),2)*16+IF(LEN(B5)<6,0,SUBSTITUTE(RIGHT(B5,4),"oz","")))

    If you aren't bothered about the sign of the difference then wrap C5 in the ABS() function
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Converting text weights to ounces

    Oeldere,
    Your solution works wonderfully! All I had to do was move the ABS function to wrap all around it, because for this exercise I'm only interested in degree of difference, not whether it's a positive or negative difference. Updated formula is like this:
    Please Login or Register  to view this content.
    Going through the formula now to make sure I understand it all. I started going down a similar road using SUBSTITUTE, but hit a dead end there. SEARCH appears to work much better.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Converting text weights to ounces

    Richard,
    You succeed with SUBSTITUTE where I failed! Great solution! Thank you.
    Last edited by jomili; 05-26-2022 at 11:22 AM.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Converting text weights to ounces

    Thanks for the rep.

    Glad I could help.

    Thanks for marking the question solved.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Converting text weights to ounces

    Please try

    =LET(s,IF({1;0},$C$2,B5),ABS(SUM(TEXT(MID(0&s,FIND({"l","o"},s&"lo")-1,2),"0;;;\0")*{16,1}*{1;-1})))
    Attached Files Attached Files

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Converting text weights to ounces

    Here is an alternate solution using Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Excel 2016 (Windows) 64 bit
    B
    16
    total ounces
    17
    155
    18
    149
    19
    160
    20
    151
    21
    147
    22
    168
    23
    164
    24
    144
    25
    167
    Sheet: Sheet1
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Converting text weights to ounces

    Bo_Ry, while I'm sure it works, even though we're on Office 365 our IT department VERY slowly rolls out upgrades, so we still don't have access to the LET function. Maybe some day.

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Converting text weights to ounces

    Alan,
    Thanks for the Power Query solution. I'm just starting to use PQ, so this will help me in my understanding.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Converting text weights to ounces

    You may want to pick up the book "Master Your Data with Power Query and Power BI" by Ken Puls and Miguel Escobar.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Converting a text and number time into a numerical value and converting to minutes
    By RQuilley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2020, 11:40 AM
  2. Converting weights
    By burnsie in forum Excel General
    Replies: 5
    Last Post: 02-04-2010, 09:48 AM
  3. Pounds and ounces ???
    By diana15 in forum Excel General
    Replies: 1
    Last Post: 10-17-2006, 07:00 PM
  4. lbs ounces drams
    By mjc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2006, 04:38 PM
  5. how do i use lbs and ounces in excel
    By Model-man in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2006, 01:10 PM
  6. Pounds to ounces
    By Fizz in forum Excel General
    Replies: 3
    Last Post: 02-02-2005, 11:06 AM
  7. Lbs and Ounces
    By treboryelbis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1