Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to
convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
If one of your four letters in in A1 then:
=(A1="p")+(A1="m")*2+(A1="d")*3 will convert it to a value. -- Gary''s Student - gsnu200765 "The IC Teacher" wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
Try this:
=MATCH(A1,{"f","p","m","d"},0)-1 "The IC Teacher" wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
On Jan 13, 8:03*am, The IC Teacher
wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 =find(A1,"fpmd")-1 If the cell might not contain any of those characters, then: =if(or(A1="",iserror(find(A1,"fpmd")), "", find(A1,"fpmd")-1) I believe a more elegant formula is possible in Excel 2007. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
Thanks Gary worked perfectly
-- The Teacher "Gary''s Student" wrote: If one of your four letters in in A1 then: =(A1="p")+(A1="m")*2+(A1="d")*3 will convert it to a value. -- Gary''s Student - gsnu200765 "The IC Teacher" wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
On Jan 13, 9:03*pm, The IC Teacher
wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher Hi Teacher, I have small confusion on your question... You mentioned that you are having one of four characters in in any cell: That means you will be having any character in each cell, Is tat right. Then you want to change those letter to values to compare the cell values, is tat right.. I hope you doubt will this: You want check both cell as having same result or not is tat correct, if so: Use match formula: =MATCH(a1,b1,0) If both having a same alphabet means answer will be 1, if not #N/A Use the fill series and drag to next cells.. Thanks and Regards, S. Praburam |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
I have one of four chars displayed in any cell "f", "p", "m", "d",
If it is only 1 of those 4 letters, perhaps another option: =MOD(2626912,CODE(A1)+1) -- HTH :) Dana DeLouis "The IC Teacher" wrote in message ... I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
joeu2004 wrote...
.... =find(A1,"fpmd")-1 If the cell might not contain any of those characters, then: =if(or(A1="",iserror(find(A1,"fpmd")), "", find(A1,"fpmd")-1) .... It makes sense to trap A1 blank, but if A1 were anything else but not one of the 4 letters in question, errors should propagate to indicate invalid entry. Also, if A1 were any multiple character substring of "fpmd", your formula would return a numeric result when it shouldn't. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert text to number in excel
Hi,
If you know you are only going to have one of these four single digit letters in A1 all the formulas supplied will work. Note that if that is not the case then none of these formulas may return the answer you want, whatever that might be. For example, =(A1="p")+(A1="m")*2+(A1="d")*3 returns 0 if A1 contains "Z" or "ma" for example. And if you entered fpmd in cell B1 you could use the shortened version of a previous suggestion: =FIND(A1,B1)-1 -- Cheers, Shane Devenshire "The IC Teacher" wrote: I have one of four chars displayed in any cell "f", "p", "m", "d", i wish to convert "f" to 0 "p" to 1 "m" to 2 and "d" to 3 then compare to a second set of cells. The second set of cells value can also be "f", "p", "m", "d" which i need to convert to the same set of numbers. i will then use autoformat in a third cell to determine if the second set of cells is higher in value than the first or lower in value The Teacher |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number convert to text in excel | Excel Discussion (Misc queries) | |||
can we convert number to text in excel as if 1= One | Excel Discussion (Misc queries) | |||
How do I convert a number in Excel to text? | Excel Worksheet Functions | |||
In Excel, how do we convert text into number (value)? | Excel Worksheet Functions | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) |