|
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="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 |
| All times are GMT +1. The time now is 11:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com