Performing math function with cells containing numbers & text

[meighkee]

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.

 

[AhoyNC]

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

Excel Workbook
	A
1	5
2	5 days
3	25
Sheet

 

[jtakw]

Hi,

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

Book1
	A	B	C	D
1	5	5	5	5
2	5 days	6 weeks	7 text	11 long years
3	25	30	35	55
Sheet577

Cell Formulas
Range	Formula
A3	=LEFT(A2,FIND(" ",A2))*A1

 

[meighkee]

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

	A
5 days

<colgroup><col style="width:30px; "><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]25[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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

 

[meighkee]

Thank you very much as well. Both these responses will help greatly.

 

[meighkee]

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

	A
5 days

<colgroup><col style="width:30px; "><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]25[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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]

Cell Formula
A3 =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"For",""),"Days",""))*A1

 

[jtakw]

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"