Convert specific text to a corresponding number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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,
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,
 
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.
 
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.
 
Back
Top