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
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
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?
Assume your string with numbers and letters is in A1, and your multiplier is in a2, you can use this:
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.Formula:Please Login or Register to view this content.
- Moo
Last edited by Moo the Dog; 10-30-2012 at 12:50 PM.
If String cell is A1, and Number cell is C1, then put this in B1:
NOTE: YOU MUST ENTER THIS FORMULA BY PRESSING SHIFT+CTRL+ENTER (On a Mac: COMMAND+ENTER)Formula:Please Login or Register to view this content.
(It is an array formula)
- Moo
Last edited by Moo the Dog; 10-30-2012 at 12:49 PM.
Sorry to be a pain but I don't how to do that. I am new to this. Please help me
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
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!
[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
[QUOTE=Bob0;2990080] Put this in C33 (remember to press CTRL+SHIFT+ENTER to make it work), and see the NOTE below:
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.Formula:Please Login or Register to view this content.
- Moo
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.
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
What a shame that not all the 'experts' and 'gurus' on here can't be as helpful as Moo
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
We try to walk the middle road on that here
Agreed, but there is a fine libne between that and being condescending. Spend some time here, and then form your opnionWhen people ask for help, its not good to assume that they know much besides basic formula rditing and loading in cell values
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
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
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.
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: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.Formula:Please Login or Register to view this content.
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.
You make a valid point, Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks