# Ignore text in cell and use SUM function

#### Kquig

##### New Member
This seems like a really silly question to me but I've searched up and down for an answer and can't seem to get it.

A1 contains: \$10 / sq ft
B1 contains: 10,000

I'd like C1 to show the product of \$10 in A1 multiplied by B1. So it needs to ignore '/ sq ft' in A1.

C1 should then contain: \$100,000

Is this not possible or am I just not looking in the right places?

Let me know, thanks!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### schielrn

##### Well-known Member
Will it always be '/ sq ft' in a cell or can there be other text?

If so, you can use this:
Excel Workbook
A
1\$10 / sq ft
210,000
3100,000.00
Sheet1
Cell Formulas
RangeFormula
A3=A2*SUBSTITUTE(A1," / sq ft","")

Hope that helps.

#### schielrn

##### Well-known Member
Or the 2nd formula will extract the numbers:
Excel Workbook
AB
1\$10 / sq ft
210,000
3100,000.00100,000.00
Sheet1
Cell Formulas
RangeFormula
A3=A2*SUBSTITUTE(A1," / sq ft","")
#VALUE!

But if you had something like:

\$10 / sq ft3

It would multiply by 103 and not 10.

Hope that make sense.

#### Kquig

##### New Member
Or the 2nd formula will extract the numbers:

Excel Workbook
AB
1\$10 / sq ft
210,000
3100,000.00100,000.00
Sheet1
Cell Formulas
RangeFormula
A3=A2*SUBSTITUTE(A1," / sq ft","")
#VALUE!
Entered with Ctrl+Shift+Enter

But if you had something like:

\$10 / sq ft3

It would multiply by 103 and not 10.

Hope that make sense.

Yeah, the text is subject to change. The number will always be a whole number, i.e. 10, 200, 399, 6, etc. and most likely will be followed by some type to indicate the measurement or unit type.

I tried the second formula and it just gave me an error.

#### schielrn

##### Well-known Member
Are you entering the 2nd formula with control+shift+enter and not just enter?

#### pgc01

##### MrExcel MVP
Hi

Another formula solution:

In B2:

=-LOOKUP(0,-MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9}))*A3

Copy to B6 and B10

#### RoryA

##### MrExcel MVP, Moderator
Or you could make life easy and put the units in a different cell.

#### tclark8

##### New Member
i tried this formula and it works:

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

#### pgc01

##### MrExcel MVP
i tried this formula and it works:

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

Hi tclark8

You are right, it works and it's much simpler. It demands, however, that the string format is exactly the one posted (ex. if there is a space between the \$ sign and the number, like "\$ 10" it will no longer work).

It will also not work if

- you use a currency symbol different from the one in your region (for ex., for me your formula only works in my table for B10, that uses €, it will not work for the others (that use \$ and £)

- the string had a different structure, like, for ex.: "sq ft: \$10"

This said, if the structure of the string is always exactly the one posted, then your formula is preferable.

#### Kquig

##### New Member
Are you entering the 2nd formula with control+shift+enter and not just enter?

I didn't at first. It works but now if the cells are blank it gives a #REF! err.

Replies
0
Views
216
Replies
5
Views
154
Replies
2
Views
141
Replies
0
Views
93
Replies
4
Views
544

1,181,649
Messages
5,931,216
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back