Performing math function with cells containing numbers & text

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
Greetings,

I need some help. Am perform basic multiplication between two cells, one containing numbers & text.

Example -
Cell A1 - contains the number 5
Cell A2 - contains the words "5 days"
Cell A3 - I'd like to multiply A1 times A2 and come up with 25.

The word "days" in A2 is creating an issue. There must be a way for Excel to see only the number 5 in A2, and perform the multiplication formula accordingly.

Am hoping someone can help.

Thank you.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,632
Office Version
  1. 365
Platform
  1. Windows
If your text will always be "days", then something like this.
Excel Workbook
A
15
25 days
325
Sheet
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

If the word(s) can be anything, but the Number is Always at the beginning (leftmost) of the Text string:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">5 days</td><td style=";">6 weeks</td><td style=";">7 text</td><td style=";">11 long years</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td><td style="text-align: right;;">35</td><td style="text-align: right;;">55</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet577</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">" ",A2</font>)</font>)*A1</td></tr></tbody></table></td></tr></table><br />
 

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
If your text will always be "days", then something like this.


A
15
25 days
325

<colgroup><col style="width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A3=SUBSTITUTE(A2,"days","")*A1

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thank you very much. Looks like it will do just fine.
 

meighkee

New Member
Joined
Sep 3, 2008
Messages
39

ADVERTISEMENT

Thank you very much. This looks like it will do just fine.
 

meighkee

New Member
Joined
Sep 3, 2008
Messages
39

ADVERTISEMENT

If your text will always be "days", then something like this.


A
15
25 days
325

<colgroup><col style="width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A3=SUBSTITUTE(A2,"days","")*A1

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Again, thanks very much for the quick response. I do, however, have one modification I'd appreciate your help with. I used the example '5 days' before but the actual cell will contain text, a number, then text again. Example: "For 5 Days". I tried modifying your formula in various ways to see if I could get it to work with those cell contents but to no avail. So, I'm back for your expertise one more time. Thank you very much.
 

meighkee

New Member
Joined
Sep 3, 2008
Messages
39
Again, thanks very much for the quick response. I do, however, have one modification I'd appreciate your help with. I used the example '5 days' before but the actual cell will contain text, a number, then text again. Example: "For 5 Days". So the number will actually be in the middle of both words. I tried modifying your formula in various ways to see if I could get it to work with those cell contents but to no avail. So, I'm back for your expertise one more time. Thank you very much.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Cell Formula
A3 =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"For",""),"Days",""))*A1
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Is that the Only possible format for your Text strings, "For # Days" ?
If not, please show a few samples of All possibilities.
I also need to know if there may be more than 1 number within the Text string, like "For # Days and # Months"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top