Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text and Dollar Value
Im trying to calculate some data in a cell that contains text and dollar
values. Ive put together an auction spread sheet for my fantasy baseball league and Im trying to keep track of players and their dollar values in the same cell. So one cell may contain Jeter, 15 which really represents Derek Jeter was sold for 15 dollars. I have formulas for keeping track of dollar values, but they wont recognize that entry because the cell contains text and a dollar value. Is there any way I can get excel to only see the numerical value and ignore the text? Thanks in advance for the help. Also, I realize I can put the number value and the name in a separate cell, but Im trying to save space with the spread sheet. Thanks again, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text and Dollar Value
It's always much easier not to mix the data. Anyway, if the data posted is
representative (one comma, followed by the embedded number) and is in A1, and you want to multiply it with a number in B1, you could try this in C1: =B1*RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jason Sewell" wrote: Im trying to calculate some data in a cell that contains text and dollar values. Ive put together an auction spread sheet for my fantasy baseball league and Im trying to keep track of players and their dollar values in the same cell. So one cell may contain Jeter, 15 which really represents Derek Jeter was sold for 15 dollars. I have formulas for keeping track of dollar values, but they wont recognize that entry because the cell contains text and a dollar value. Is there any way I can get excel to only see the numerical value and ignore the text? Thanks in advance for the help. Also, I realize I can put the number value and the name in a separate cell, but Im trying to save space with the spread sheet. Thanks again, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text and Dollar Value
If cells will always have these features:
Text (without commas) followed by one comma and a space ending with a numeric value ....or be blank... Try this: For that structure in cell A1: Jeter, 15 B1: =--("0"&MID(A1,FIND(", ",A1&", ")+2,255)) copy that formula down as far as you need. That formula is durable agains blank cells. If you want the sum of the numeric values from a range of those cells.. A1: jeter, 15 A2: Alpha, 12.5 A3: Bravo, 20.33 etc This formula sums the numeric endings of A1:A10: =SUMPRODUCT(--("0"&MID(A1:A10,FIND(", ",A1:A10&", ")+2,255))) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Jason Sewell" <Jason wrote in message ... I'm trying to calculate some data in a cell that contains text and dollar values. I've put together an auction spread sheet for my fantasy baseball league and I'm trying to keep track of players and their dollar values in the same cell. So one cell may contain "Jeter, 15" which really represents Derek Jeter was sold for 15 dollars. I have formulas for keeping track of dollar values, but they won't recognize that entry because the cell contains text and a dollar value. Is there any way I can get excel to only see the numerical value and ignore the text? Thanks in advance for the help. Also, I realize I can put the number value and the name in a separate cell, but I'm trying to save space with the spread sheet. Thanks again, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Text and Dollar Value
hi
Jeter, 15 has a comma and a space. if all your entries have the same format, you could use this formula =RIGHT(B1,LEN(B1)-FIND(" ",B1,1)) regards FSt1 "Jason Sewell" wrote: Im trying to calculate some data in a cell that contains text and dollar values. Ive put together an auction spread sheet for my fantasy baseball league and Im trying to keep track of players and their dollar values in the same cell. So one cell may contain Jeter, 15 which really represents Derek Jeter was sold for 15 dollars. I have formulas for keeping track of dollar values, but they wont recognize that entry because the cell contains text and a dollar value. Is there any way I can get excel to only see the numerical value and ignore the text? Thanks in advance for the help. Also, I realize I can put the number value and the name in a separate cell, but Im trying to save space with the spread sheet. Thanks again, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to dollar | Excel Discussion (Misc queries) | |||
How do I assign dollar value to text | New Users to Excel | |||
convert $1.00 to text one dollar. | Excel Worksheet Functions | |||
convert $1.00 to text one dollar. | Excel Worksheet Functions | |||
Dollar text | Excel Discussion (Misc queries) |