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.

 

[meighkee]

Thank you very much rlv01.

 

[meighkee]

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"

Yes, that is the only format for my text string. It will always be "For # Days". There will only be a number once in the string, as per my example. The number of days, of course, won't always be 5. Could be 10, 12, 20, etc.

 

[sandy666]

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

19​

[/td][td]

5 day(s)​

[/td][td]

5​

[/td][td]

25​

[/td][td]=M19*N19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

20​

[/td][td]

25 day(s)​

[/td][td]

10​

[/td][td]

2.5​

[/td][td]=M20/N20[/td][/tr]
[/table]

column M custom format: 0 "day(s)"
then type number in M column

 

[jtakw]

Yes, that is the only format for my text string. It will always be "For # Days". There will only be a number once in the string, as per my example. The number of days, of course, won't always be 5. Could be 10, 12, 20, etc.

Book1
	A	B	C
1	5	5	5
2	For 5 days	For 16 days	For 363 days
3	25	80	1815
Sheet577

Cell Formulas
Range	Formula
A3	=MID(A2,5,LEN(A2)-9)*A1

 

[meighkee]

Thanks very much jtakw. Can you explain the formula for me as I need to make some minor adjustments to cell numbers, etc.

Thanks again.