Formatting Cells to Ignore Text Within a Cell

[BBA18]

How can I get Excel to ignore text in a cell that I am referencing?

For example, how would I get cell A3 to return a value of 200 by using the formula "A1*A2"?

A1: 2 Bagels
A2: 100
A3: ??

To take it one step further, how would I get cell A3 to return a value of 35 by using the formula "A1*A2"

A1: $10 less $5 that I lost on the bus
A2: 7
A3: ???

 

[PaddyD]

You need to exploit regularities in the data.

For the first condition:

=--LEFT(A1,FIND(" ",A1)-1)*A2

...which assumes that numbers turn up before the first space in the string.

For the second condition, is the following true:

the text string will always have two $ amounts (preceded by a $ and followed by a space); find them and subtract the second from the first.

...because formula that copes with that won't cope with other structures...

In any case, it is better to avoid all these formula gymnastics by keeping text and numbers separate.

 

[BBA18]

You need to exploit regularities in the data.

For the first condition:

=--LEFT(A1,FIND(" ",A1)-1)*A2

...which assumes that numbers turn up before the first space in the string.

For the second condition, is the following true:

the text string will always have two $ amounts (preceded by a $ and followed by a space); find them and subtract the second from the first.

...because formula that copes with that won't cope with other structures...

In any case, it is better to avoid all these formula gymnastics by keeping text and numbers separate.

I'm not trying to have the text ignored in the cell when I reference it for a formula per se.

I would like cell A1 to show "2 Bagels" on the workbook sheet, but when I select cell A1 I would like to see "2" in the formula bar.

 

[FDibbins]

Try a custom format.

just enter 2 in the cell, and use Custom Formatting # "bagels"