convert text to number in excel

[The IC Teacher]

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

 

[Gary''s Student]

If one of your four letters in in A1 then:

=(A1="p")+(A1="m")*2+(A1="d")*3

will convert it to a value.

 

[Teethless mama]

Try this:

=MATCH(A1,{"f","p","m","d"},0)-1

 

[joeu2004]

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.

 

[The IC Teacher]

Thanks Gary worked perfectly
--
The Teacher

If one of your four letters in in A1 then:

=(A1="p")+(A1="m")*2+(A1="d")*3

will convert it to a value.

 

[Praburam]

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

 

[Dana DeLouis]

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)

 

[Harlan Grove]

=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.

 

[ShaneDevenshire]

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