+ Reply to Thread
Results 1 to 25 of 25

Multiply 2 cells, one of which has text

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Multiply 2 cells, one of which has text

    I want to multiply 2 cells. One cell has text then numbers. What formula would I use to ignore the text and multiply the numbers?
    SOLVED
    Last edited by Bob0; 10-31-2012 at 08:52 AM. Reason: SOLVED

  2. #2
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: Multiply 2 cells, one of which has text

    You will have to extract the number using a formula or two, and then multiply.

    Does your data follow any kind of pattern? Can you post an example sheet?

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    Assume your string with numbers and letters is in A1, and your multiplier is in a2, you can use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note: The numbers in the string cell (A1) must be together for my formula to work. If you have ABC123sg, it will work fine. If you have abc12sg3, it won't work. Also, must enter this formula by pressing Shift+Ctrl+Enter since it is an array formula.

    - Moo
    Last edited by Moo the Dog; 10-30-2012 at 12:50 PM.

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    Quote Originally Posted by mshale View Post
    You will have to extract the number using a formula or two, and then multiply.

    Does your data follow any kind of pattern? Can you post an example sheet?
    Hi mshale. Cell a contains a name followed by a number. Cell c has a number only. I want the answer to be in cell b.

  5. #5
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    Quote Originally Posted by Moo the Dog View Post
    Assume your string with numbers and letters is in A1, and your multiplier is in a2, you can use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note: The numbers in the string cell (A1) must be together for my formula to work. If you have ABC123sg, it will work fine. If you have abc12sg3, it won't work.

    - Moo
    Hi Moo. Cell a contains a name followed by a number. Cell c has a number only. I want the answer to be in cell b.

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    If String cell is A1, and Number cell is C1, then put this in B1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    NOTE: YOU MUST ENTER THIS FORMULA BY PRESSING SHIFT+CTRL+ENTER (On a Mac: COMMAND+ENTER)
    (It is an array formula)
    - Moo
    Last edited by Moo the Dog; 10-30-2012 at 12:49 PM.

  7. #7
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    Sorry to be a pain but I don't how to do that. I am new to this. Please help me

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiply 2 cells, one of which has text

    he just told you how. copy the formula to the formula bar. then, instead of hitting ENTER, hold down SHIFT and CTRL together, and then him ENTER while you keep them held down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    Quote Originally Posted by Moo the Dog View Post
    If String cell is A1, and Number cell is C1, then put this in B1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    NOTE: YOU MUST ENTER THIS FORMULA BY PRESSING SHIFT+CTRL+ENTER (On a Mac: COMMAND+ENTER)
    (It is an array formula)
    - Moo
    This formula works fine in row 1. Will you please tell me how to apply it to more
    rows

  10. #10
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    Bob,

    If you have the formula in cell B1, select cell B1. You will notice a small square at the bottom-right corner of that cell, if you click and hold the mouse button down while you drag (move the mouse) downwards, it will copy that formula down into the cells below for as far as you want it to go

    Hope that helps,
    Moo

    - - - - - - - - - -
    Please remember to mark the thread as [SOLVED} if your question(s) have been adequately answered. To do so, click on the link for 'Thread Tools' just above your original post and select "Mark Thread as Solved".

    Also, a good way to thank those who have helped is to click on the star below their name in one of their posts. It's always appreciated!

  11. #11
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    [QUOTE=Moo the Dog;2989984]Bob,

    If you have the formula in cell B1, select cell B1. You will notice a small square at the bottom-right corner of that cell, if you click and hold the mouse button down while you drag (move the mouse) downwards, it will copy that formula down into the cells below for as far as you want it to go

    Hope that helps,
    Moo

    - - - - - - - - - -

    Thanks Moo

    Your a STAR

  12. #12
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    [QUOTE=Bob0;2990033]
    Quote Originally Posted by Moo the Dog View Post
    Bob,

    If you have the formula in cell B1, select cell B1. You will notice a small square at the bottom-right corner of that cell, if you click and hold the mouse button down while you drag (move the mouse) downwards, it will copy that formula down into the cells below for as far as you want it to go

    Hope that helps,
    Moo

    - - - - - - - - - -

    Thanks Moo

    Your a STAR
    Sorry Moo, I gave you the wrong cell numbers.It should have been :B33 letters+numbers. C33 answer. D33 numbers. What part of the formula do I need to change?

  13. #13
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    [QUOTE=Bob0;2990080]
    Quote Originally Posted by Bob0 View Post

    Sorry Moo, I gave you the wrong cell numbers.It should have been :B33 letters+numbers. C33 answer. D33 numbers. What part of the formula do I need to change?
    Put this in C33 (remember to press CTRL+SHIFT+ENTER to make it work), and see the NOTE below:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    NOTE: The numbers in red text (25 - it appears twice) needs to be higher than the length of your longest string. For example if the longest string (total # of characters) in any cell in column B is 20, then the numbers in red need to be HIGHER than 20. I've set it to 25, but if you need to go higher, just make the change.

    - Moo

  14. #14
    Registered User
    Join Date
    10-30-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    [QUOTE=Moo the Dog;2990256]
    Quote Originally Posted by Bob0 View Post
    Put this in C33 (remember to press CTRL+SHIFT+ENTER to make it work), and see the NOTE below:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    NOTE: The numbers in red text (25 - it appears twice) needs to be higher than the length of your longest string. For example if the longest string (total # of characters) in any cell in column B is 20, then the numbers in red need to be HIGHER than 20. I've set it to 25, but if you need to go higher, just make the change.

    - Moo
    Moo

    You have certainly got a lot of patience. Thanks once again for sorting out my problem.

  15. #15
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    You're welcome, Bob.

    - Moo

    - - - - - - - - - -
    If you are satisfied with the solution(s) provided, please mark the thread as [SOLVED] by clicking on the words 'Thread Tools' above your 1st post in this thread. Then select 'Mark Thread as Solved'.

    Also, a good way to 'Say thanks' to those who have helped is to click on the star below their name in one of their posts. It's always appreciated.

  16. #16
    Registered User
    Join Date
    01-29-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Multiply 2 cells, one of which has text

    Hi Moo,

    I joined the forum to check out your formula as the OP had the same issue as myself and you formula works a treat, thank you very much, that has saved me hours of going through hundreds of cells doing =100*$A$1 as an example.

    Thank you again

  17. #17
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    Quote Originally Posted by Khamelion View Post
    Hi Moo,

    I joined the forum to check out your formula as the OP had the same issue as myself and you formula works a treat, thank you very much, that has saved me hours of going through hundreds of cells doing =100*$A$1 as an example.

    Thank you again
    Glad it works for you. - Moo

  18. #18
    Registered User
    Join Date
    02-06-2014
    Location
    Central Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    What a shame that not all the 'experts' and 'gurus' on here can't be as helpful as Moo

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiply 2 cells, one of which has text

    Quote Originally Posted by Limoman View Post
    What a shame that not all the 'experts' and 'gurus' on here can't be as helpful as Moo
    Limoman, I am unsure as to your intent with that comment? Perhaps if you spent a bit of time here, you would re-assess your opnion? 5 posts and 1 day is hardly anything to base on?

  20. #20
    Registered User
    Join Date
    02-06-2014
    Location
    Central Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiply 2 cells, one of which has text

    because i looked around, did lots of reading while searching for an answer, and saw lots of condescending replies
    Dont worry, Its common on forums everywhere, but i got the feeling a lot of people asking for help are very basic users, so even something simple like 'put it in a macro' draws looks of puzzlement.
    When people ask for help, its not good to assume that they know much besides basic formula rditing and loading in cell values

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiply 2 cells, one of which has text

    We try to walk the middle road on that here

    When people ask for help, its not good to assume that they know much besides basic formula rditing and loading in cell values
    Agreed, but there is a fine libne between that and being condescending. Spend some time here, and then form your opnion

    Anyway, this is not the place for a duscussion like this. If you wish to continue, please start your own thread in teh water cooler forum

  22. #22
    Registered User
    Join Date
    03-29-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    1

    Re: Multiply 2 cells, one of which has text

    Hi guys, sorry to revive such an old thread. Like Khamelion said, thank you to Moo for the formula. However I'm having a bit of difficulty with it.

    I have a group of cells in the format of [ US$ 4.72*/ Pieces ] and will be multiplying them by different numbers

    By taking out the *A2 at the end of the formula I've got a number but they are rounded down to 4.70 in this instance. Can anyone help to make it the actual value rather than a rounded one?

    Thanks

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiply 2 cells, one of which has text

    excelrandom welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  24. #24
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Multiply 2 cells, one of which has text

    I think of ExcelRandom's question isn't so much a "new" question, but a slight variant of the original formula I provided, and in this rare case I can offer a possible solution.

    Since your numbers include decimals, try modifying the formula as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That would assume that your data cell is A1, and the maximum # of characters in the cell is 25. By adding the +1 at the end, it includes the decimal.

    Note, if any of your cells have a number without a decimal, this is going to cause issues.

    Also remember that this is an array formula, applied using CTRL + SHIFT + ENTER, instead of just ENTER.

    Hope this helps.

    - Moo
    Last edited by Moo the Dog; 03-29-2015 at 02:24 PM.

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiply 2 cells, one of which has text

    You make a valid point, Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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