| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Hi All,
Please help!! My problem is the following: I have text in a column like this A1 BTEC National Diploma AS Economics GCSE Maths What I would like to happen in the next column is a formula like the following: IF A1 = "BTEC National" then A2 = 1 ELSE IF A1 = "AS" then A2 = 2 ELSE IF A1 = "GCSE" then A2 = 3 I am a little stumped as to how achieve this. Any ideas? Thank you in advance! Mary. -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Hi
Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)="G ",3,""))) -- Regards Roger Govier "sike11 via OfficeKB.com" <u21678@uwe wrote in message news:6c83ffa63fd02@uwe... Hi All, Please help!! My problem is the following: I have text in a column like this A1 BTEC National Diploma AS Economics GCSE Maths What I would like to happen in the next column is a formula like the following: IF A1 = "BTEC National" then A2 = 1 ELSE IF A1 = "AS" then A2 = 2 ELSE IF A1 = "GCSE" then A2 = 3 I am a little stumped as to how achieve this. Any ideas? Thank you in advance! Mary. -- Message posted via http://www.officekb.com |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Hi Roger,
Thanks for the help! I tried this but it gave me an error "#VALUE". Any other ideas? Regards, Mary Roger Govier wrote: Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)=" G",3,""))) Hi All, [quoted text clipped - 23 lines] Mary. -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Roger missed something from the LEFT functions - try this:
=IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT(A 1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT( A1,1)="G",3,""))) Hope this helps. Pete sike11 via OfficeKB.com wrote: Hi Roger, Thanks for the help! I tried this but it gave me an error "#VALUE". Any other ideas? Regards, Mary Roger Govier wrote: Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)=" G",3,""))) Hi All, [quoted text clipped - 23 lines] Mary. -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Hi Pete,
This worked a treat!!! Thank you very much. Mary. Pete_UK wrote: Roger missed something from the LEFT functions - try this: =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT( A1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT (A1,1)="G",3,""))) Hope this helps. Pete Hi Roger, [quoted text clipped - 17 lines] Mary. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Thanks for feeding back. In future, please do not multi-post.
Pete sike11 via OfficeKB.com wrote: Hi Pete, This worked a treat!!! Thank you very much. Mary. Pete_UK wrote: Roger missed something from the LEFT functions - try this: =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT( A1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT (A1,1)="G",3,""))) Hope this helps. Pete Hi Roger, [quoted text clipped - 17 lines] Mary. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Apologies, missed a closing parenthesis after the second Left function
=IF(LEFT(A1)="B",1,IF(LEFT(A1)="A",2,IF(LEFT(A1)=" G",3,""))) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)="G ",3,""))) -- Regards Roger Govier "sike11 via OfficeKB.com" <u21678@uwe wrote in message news:6c83ffa63fd02@uwe... Hi All, Please help!! My problem is the following: I have text in a column like this A1 BTEC National Diploma AS Economics GCSE Maths What I would like to happen in the next column is a formula like the following: IF A1 = "BTEC National" then A2 = 1 ELSE IF A1 = "AS" then A2 = 2 ELSE IF A1 = "GCSE" then A2 = 3 I am a little stumped as to how achieve this. Any ideas? Thank you in advance! Mary. -- Message posted via http://www.officekb.com |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Sorry :)
Pete_UK wrote: Thanks for feeding back. In future, please do not multi-post. Pete Hi Pete, [quoted text clipped - 25 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200701/1 -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
You may be less stumped if you checked the answer in you other post
"sike11 via OfficeKB.com" wrote: Hi All, Please help!! My problem is the following: I have text in a column like this A1 BTEC National Diploma AS Economics GCSE Maths What I would like to happen in the next column is a formula like the following: IF A1 = "BTEC National" then A2 = 1 ELSE IF A1 = "AS" then A2 = 2 ELSE IF A1 = "GCSE" then A2 = 3 I am a little stumped as to how achieve this. Any ideas? Thank you in advance! Mary. -- Message posted via http://www.officekb.com |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Pete, Just an FYI..
You say Roger "missed something", not sure but maybe you thought it might be the 2nd parameter of the Left() function.. (anyway) By Default if no 2nd parameter is given, 1 is assumed. Try it In B1 enter: =Left(A1) HTH, Jim "Pete_UK" wrote in message ups.com: Roger missed something from the LEFT functions - try this: =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT(A 1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT( A1,1)="G",3,""))) Hope this helps. Pete sike11 via OfficeKB.com wrote: Hi Roger, Thanks for the help! I tried this but it gave me an error "#VALUE". Any other ideas? Regards, Mary Roger Govier wrote: Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)=" G",3,""))) Hi All, [quoted text clipped - 23 lines] Mary. -- Message posted via http://www.officekb.com |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Looks like this is a reply to a different question...
"JMay" wrote in message ... Pete, Just an FYI.. You say Roger "missed something", not sure but maybe you thought it might be the 2nd parameter of the Left() function.. (anyway) By Default if no 2nd parameter is given, 1 is assumed. Try it In B1 enter: =Left(A1) HTH, Jim "Pete_UK" wrote in message ups.com: Roger missed something from the LEFT functions - try this: =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT(A 1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT( A1,1)="G",3,""))) Hope this helps. Pete sike11 via OfficeKB.com wrote: Hi Roger, Thanks for the help! I tried this but it gave me an error "#VALUE". Any other ideas? Regards, Mary Roger Govier wrote: Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)=" G",3,""))) Hi All, [quoted text clipped - 23 lines] Mary. -- Message posted via http://www.officekb.com |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
But it sounds like it would fix your problem.
With A1 the activecell format|conditional formatting formula is: =left(a1,1)="1" Format for the first grade Add a rule and formatting for the 2nd, then the third. By using =Left(), you only need to worry about that first character. James E Middleton wrote: Looks like this is a reply to a different question... "JMay" wrote in message ... Pete, Just an FYI.. You say Roger "missed something", not sure but maybe you thought it might be the 2nd parameter of the Left() function.. (anyway) By Default if no 2nd parameter is given, 1 is assumed. Try it In B1 enter: =Left(A1) HTH, Jim "Pete_UK" wrote in message ups.com: Roger missed something from the LEFT functions - try this: =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,1)="A",2,IF(LEFT(A 1,1)="G",3,""))) or, if you are likely to have A levels as well as AS in the futu =IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT( A1,1)="G",3,""))) Hope this helps. Pete sike11 via OfficeKB.com wrote: Hi Roger, Thanks for the help! I tried this but it gave me an error "#VALUE". Any other ideas? Regards, Mary Roger Govier wrote: Hi Rather than having to type the whole description of what is in cell A1, you could just use the first character as in the formula below Enter in A2 =IF(LEFT(A1)="B",1,IF(LEFT(A1="A",2,IF(LEFT(A1)=" G",3,""))) Hi All, [quoted text clipped - 23 lines] Mary. -- Message posted via http://www.officekb.com -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting a range with conditional formatting | Excel Worksheet Functions | |||
| conditional formatting glitches | Excel Discussion (Misc queries) | |||
| Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
| conditional formatting | Excel Discussion (Misc queries) | |||
| cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions | |||
Linear Mode
