| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number/Text combination formula
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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 "donnae" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
(all in one line)
=(LEFT(A1,FIND(CHAR(32),A1)-1))*(TRIM(RIGHT(SUBSTITUTE(A1,CHAR(32),REPT(CHAR(3 2),LEN(A1))),LEN(A1)))) If this post helps click Yes --------------- Jacob Skaria "donnae" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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? 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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 --------------- Jacob Skaria "donnae" wrote: 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? 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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" wrote: 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? 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
Did'nt work....I'm still getting the Value Error Message.....
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "donnae" wrote: 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? 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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 --------------- Jacob Skaria "donnae" wrote: Did'nt work....I'm still getting the Value Error Message..... "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "donnae" wrote: 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? 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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number/Text combination formula
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. -- Rick (MVP - Excel) "donnae" wrote in message ... 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? 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combination text/number input mask | Excel Discussion (Misc queries) | |||
| Number generator and combination | Excel Discussion (Misc queries) | |||
| combination of six digit number from 0 to 49 | Excel Worksheet Functions | |||
| Number Combination finder | Excel Worksheet Functions | |||
| Excel: Allow combination of formula answer and text in one cell | Excel Worksheet Functions | |||
Linear Mode
