+ Reply to Thread
Results 1 to 4 of 4

multiplying a number in a cell containing both a number and text

  1. 10-23-2017, 07:45 AM #1
    Skyfawn
    Skyfawn is offline
    Registered User
    Join Date
    10-23-2017
    Location
    Co.Antrim, Northern Ireland
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    7

    multiplying a number in a cell containing both a number and text

    Hi my first of what will probably be several questions about a project I am working on.

    I am looking to do the following calculation,
    in the worksheet Calculations, cell K9 I would like to calculate the combination of the worksheet Meal Planner(Day) cell F8 and G8
    Now, cell G8 will only ever contain a number whereas cell F8 will always contain a number followed by Text i.e. 1 Egg; 1 Slice; 1 Gram; 1 Serving etc.
    so if F8 is 1 Egg and G8 is 3 then I would need K9 to read 3 Egg (adding a plural would be a bonus).

    Thanks in advance for any help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-23-2017, 08:09 AM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: multiplying a number in a cell containing both a number and text

    try =LEFT(F8,SEARCH(" ",F8)-1)*G8

    =LEFT('Meal Planner(Day)'!F8,SEARCH(" ",'Meal Planner(Day)'!F8)-1)*'Meal Planner(Day)'!G8
    Last edited by sandy666; 10-23-2017 at 08:16 AM.
    Reply With QuoteRegister To Reply
  3. 10-23-2017, 08:17 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: multiplying a number in a cell containing both a number and text

    Is that really what you want??

    =IF(LEFT(F8,SEARCH(" ",F8))*G8>1,LEFT(F8,SEARCH(" ",F8))*G8&MID(F8,SEARCH(" ",F8),255)&"s",LEFT(F8,SEARCH(" ",F8))*G8&MID(F8,SEARCH(" ",F8),255))

    Don't you want gram/grams???
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 10-23-2017 at 08:22 AM.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 10-23-2017, 09:48 AM #4
    Skyfawn
    Skyfawn is offline
    Registered User
    Join Date
    10-23-2017
    Location
    Co.Antrim, Northern Ireland
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    7

    Re: multiplying a number in a cell containing both a number and text

    Thanks Glenn that seems to be the calculation I was looking for, the reason I wanted the calculation on K9 of the calculations sheet though was because my ambition is that eventually I want to be able to transfer all the data out day by day to a weekly sheet and clear all the cells on the MealPlanner(Day) sheet ready for the next day, so for the purposes of planning the overall serving size it could stay in the background until it was needed.

    [Edit] ok this seems to be working =IFERROR(IF(LEFT('Meal Planner(Day)'!$F8,SEARCH(" ",'Meal Planner(Day)'!$F8))*'Meal Planner(Day)'!$G8>1,LEFT('Meal Planner(Day)'!$F8,SEARCH(" ",'Meal Planner(Day)'!$F8))*'Meal Planner(Day)'!$G8&MID('Meal Planner(Day)'!$F8,SEARCH(" ",'Meal Planner(Day)'!$F8),255)&"s",LEFT('Meal Planner(Day)'!$F8,SEARCH(" ",'Meal Planner(Day)'!$F8))*'Meal Planner(Day)'!$G8&MID('Meal Planner(Day)'!$F8,SEARCH(" ",'Meal Planner(Day)'!$F8),255)),"")

    Cheers Glenn.
    Last edited by Skyfawn; 10-23-2017 at 10:09 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiplying a cell as a whole number
    By sobcr1864 in forum Excel General
    Replies: 5
    Last Post: 02-28-2015, 09:42 AM
  2. Matching the number in one cell to the number in text string to return a value
    By jkmcn69 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2015, 09:07 AM
  3. [SOLVED] Cell with text and number, not rounding to closest full number
    By cblp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 07:51 PM
  4. [SOLVED] Formula to count the number of spaces before text/number is written in a cell.
    By kmis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-12-2013, 05:05 PM
  5. [SOLVED] find first number in a cell and move that number and all following text to the next column
    By mikets in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2012, 02:16 PM
  6. Multiplying a cell number by a user input number
    By awalsh in forum Excel General
    Replies: 3
    Last Post: 08-26-2010, 09:42 AM
  7. multiplying a cell by a set number
    By gunny86 in forum Excel General
    Replies: 2
    Last Post: 09-07-2005, 03:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1