Convert specific text to a corresponding number

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="FR")*1)
Hope this helps.

--
Andy.


"slh" <slh@discussions.microsoft.com> wrote in message
news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com...
>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="FR")*1)
> Hope this helps.
>
> --
> Andy.
>
>
> "slh" <slh@discussions.microsoft.com> wrote in message
> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com...
> >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" <slh@discussions.microsoft.com> wrote in message
news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com...
> 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="FR")*1)
>> Hope this helps.
>>
>> --
>> Andy.
>>
>>
>> "slh" <slh@discussions.microsoft.com> wrote in message
>> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com...
>> >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" <slh@discussions.microsoft.com> wrote in message
> news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com...
> > 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="FR")*1)
> >> Hope this helps.
> >>
> >> --
> >> Andy.
> >>
> >>
> >> "slh" <slh@discussions.microsoft.com> wrote in message
> >> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com...
> >> >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="FR")*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" <slh@discussions.microsoft.com> wrote in message
news:E15A8418-981A-4382-9CFB-65C8D832B3A7@microsoft.com...
> 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" <slh@discussions.microsoft.com> wrote in message
>> news:61CBCB89-BEC3-4705-AC9E-A3BB5EE2B622@microsoft.com...
>> > 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="FR")*1)
>> >> Hope this helps.
>> >>
>> >> --
>> >> Andy.
>> >>
>> >>
>> >> "slh" <slh@discussions.microsoft.com> wrote in message
>> >> news:6A054846-2221-48F8-A143-DD3B2CCB7F65@microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>