I have a field that can contain one of four different text values, BV, BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and FR=1.
Can I do this with one formula?
--
slh

Hi

To do them in situ, you would need a macro, or use Find/Replace 4 times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values, BV, BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and
FR=1.
Can I do this with one formula?
--
slh

Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4 times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values, BV, BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and
FR=1.
Can I do this with one formula?
--
slh

Pleased to help and thanks for the feedback!

--
Andy.


"slh" wrote in message
...
Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4 times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values, BV,
BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and
FR=1.
Can I do this with one formula?
--
slh

Andy,
A couple of questions if I may,
1. I have another similar situation that I tried to modify your formula to
accomplish with no luck. This time it is text converted to text. Ex "P001"
or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH".

Also can you tell me what the *means in the formula you gave me.
Thanks,
--
slh


"Andy B" wrote:

Pleased to help and thanks for the feedback!

--
Andy.


"slh" wrote in message
...
Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4 times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values, BV,
BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6 and
FR=1.
Can I do this with one formula?
--
slh

Hi

The way the formula works is by treating each part of the formula as a sum.
The first bit looks whether A2="BV" and returns a TRUE or a FALSE - which is
then coerced into being a 1 or a 0 by multiplying (*) it by whichever value
you want the result to be. If BR was in A2, for example, the formula:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
would create a sum of:
=(0*5)+(1*4)+(0*6)+(0*1) which gives a result of 4.
With a text result, however, you'll need to use IF statements - unles there
are a lot of options.
=IF(OR(A2="P001",A2="P002"),"SPER",IF(OR(A2="P004" ,A2="P005"),"MPER",IF(A2="P102","MH","None
of these")))
Hope this helps.

--
Andy.


"slh" wrote in message
...
Andy,
A couple of questions if I may,
1. I have another similar situation that I tried to modify your formula
to
accomplish with no luck. This time it is text converted to text. Ex
"P001"
or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH".

Also can you tell me what the *means in the formula you gave me.
Thanks,
--
slh


"Andy B" wrote:

Pleased to help and thanks for the feedback!

--
Andy.


"slh" wrote in message
...
Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4
times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values,
BV,
BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6
and
FR=1.
Can I do this with one formula?
--
slh

Thankyou for taking the time to explain, that helps me a lot! I am a bit of
a novice with formulas so I really appreciate it.
--
slh


"Andy B" wrote:

Hi

The way the formula works is by treating each part of the formula as a sum.
The first bit looks whether A2="BV" and returns a TRUE or a FALSE - which is
then coerced into being a 1 or a 0 by multiplying (*) it by whichever value
you want the result to be. If BR was in A2, for example, the formula:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
would create a sum of:
=(0*5)+(1*4)+(0*6)+(0*1) which gives a result of 4.
With a text result, however, you'll need to use IF statements - unles there
are a lot of options.
=IF(OR(A2="P001",A2="P002"),"SPER",IF(OR(A2="P004" ,A2="P005"),"MPER",IF(A2="P102","MH","None
of these")))
Hope this helps.

--
Andy.


"slh" wrote in message
...
Andy,
A couple of questions if I may,
1. I have another similar situation that I tried to modify your formula
to
accomplish with no luck. This time it is text converted to text. Ex
"P001"
or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH".

Also can you tell me what the *means in the formula you gave me.
Thanks,
--
slh


"Andy B" wrote:

Pleased to help and thanks for the feedback!

--
Andy.


"slh" wrote in message
...
Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4
times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values,
BV,
BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4, FM=6
and
FR=1.
Can I do this with one formula?
--
slh

No problem!

--
Andy.


"slh" wrote in message
...
Thankyou for taking the time to explain, that helps me a lot! I am a bit
of
a novice with formulas so I really appreciate it.
--
slh


"Andy B" wrote:

Hi

The way the formula works is by treating each part of the formula as a
sum.
The first bit looks whether A2="BV" and returns a TRUE or a FALSE - which
is
then coerced into being a 1 or a 0 by multiplying (*) it by whichever
value
you want the result to be. If BR was in A2, for example, the formula:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
would create a sum of:
=(0*5)+(1*4)+(0*6)+(0*1) which gives a result of 4.
With a text result, however, you'll need to use IF statements - unles
there
are a lot of options.
=IF(OR(A2="P001",A2="P002"),"SPER",IF(OR(A2="P004" ,A2="P005"),"MPER",IF(A2="P102","MH","None
of these")))
Hope this helps.

--
Andy.


"slh" wrote in message
...
Andy,
A couple of questions if I may,
1. I have another similar situation that I tried to modify your
formula
to
accomplish with no luck. This time it is text converted to text. Ex
"P001"
or "P002" = "SPER", "P004" or "P005" = "MPER" and "P102" = "MH".

Also can you tell me what the *means in the formula you gave me.
Thanks,
--
slh


"Andy B" wrote:

Pleased to help and thanks for the feedback!

--
Andy.


"slh" wrote in message
...
Thanks Andy,
The formula to list them in an adjacent column worked perfectly.
--
slh


"Andy B" wrote:

Hi

To do them in situ, you would need a macro, or use Find/Replace 4
times.
To do them in an adjacent column, you could use something like:
=((A2="BV")*5)+((A2="BR")*4)+((A2="FM")*6)+((A2="F R")*1)
Hope this helps.

--
Andy.


"slh" wrote in message
...
I have a field that can contain one of four different text values,
BV,
BR,
FM, FR. I need to convert these to a number, Ex: BV=5, BR=4,
FM=6
and
FR=1.
Can I do this with one formula?
--
slh