Number/Text combination formula

[donnae]

How do I create a formula that gives a dollar amount as the ending result in
a cell when the cell that formula is referencing contains numbers and text?
Example 5 Boxes at 5.99= $29.95($0.00 dollar amount being the end result 5
boxes being the number/text cell reference for the formula) Anyhelp anyone
can provide is greatly appreciated!

 

[Jacob Skaria]

I am not sure I understood your query correctly..If you are looking to find
the result from the data in cell A1; try the below

A1 = 5 Boxes at 5.99

=(LEFT(A1,FIND(" ",A1)-1)+0)*(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",LEN(A1))),LEN(A1)))+0)

If this post helps click Yes

 

[Jacob Skaria]

(all in one line
=(LEFT(A1,FIND(CHAR(32),A1)-1))*(TRIM(RIGHT(SUBSTITUTE(A1,CHAR(32),REPT(CHAR(32),LEN(A1))),LEN(A1))))

If this post helps click Yes

 

[donnae]

Hi Jacob thanks for the quick response, lets see if I can articulate a little
clearer. This is how my spread sheet is set up:
Quantity Unit Cost Total Cost
5 Boxes(Coloum/Cell # A1) $5.29(A2) 26.45(A3)
My formula currently looks like this =A1*A2 . I get a VALUE ERROR message
because cell A1 contains text! How do I create a formula that will ignore the
text portion of cell A1 and give me the total dollar amount in cell A3?

 

[Jacob Skaria]

If you have a space between the numeric part and text in cell A1 use the
formula

=(LEFT(A1,FIND(CHAR(32),A1)-1)) * A2

If there are no spaces use the below formula;Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=LEFT(A1,COUNT(1*MID(A1,ROW($1:$9),1)))*A2

If this post helps click Yes

 

[Francis]

perhap this
assuming that data start at row 2 onward, place this in C2 and copy down
=LEFT(A2,SEARCH(" ",A2,1))*B2

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[donnae]

Did'nt work....I'm still getting the Value Error Message.....

 

[Jacob Skaria]

Try out the below test and feedback..

ColA ColB ColC
5 books 20 =(LEFT(A2,FIND(CHAR(32),A2)-1))*B2

If this post helps click Yes

 

[Rick Rothstein]

Try this formula in A3...

=A2*LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

By the way, it would have been clearer had you listed your data vertically
(since your data is all in Column A) instead of horizontally making it look
like your data was all in Row 2.