Performing math function with cells containing numbers & text

meighkee

New Member
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.

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
If your text will always be "days", then something like this.
Excel Workbook
A
15
25 days
325
Sheet

jtakw

Well-known Member
Hi,

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

meighkee

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

 A 1 5 2 5 days 3 25

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

 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

New Member

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

meighkee

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

meighkee

New Member

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

 A 1 5 2 5 days 3 25

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

 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

New Member
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
Cell Formula
A3 =TRIM(SUBSTITUTE(SUBSTITUTE(A2,"For",""),"Days",""))*A1

jtakw

Well-known Member
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:

Replies
4
Views
124
Replies
4
Views
64
Replies
4
Views
52
Replies
3
Views
58
Replies
3
Views
51