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

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

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

On Jan 13, 8:03*am, The IC Teacher
<TheICTeac...@discussions.microsoft.com> 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.

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

On Jan 13, 9:03*pm, The IC Teacher
<TheICTeac...@discussions.microsoft.com> 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

>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" <(E-Mail Removed)> wrote in message
news:65C36BE3-10E8-4D26-AC64-(E-Mail Removed)...
>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

joeu2004 <joeu2...@hotmail.com> 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.

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