Conditional Formatting

[sike11 via OfficeKB.com]

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.

 

[Roger Govier]

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,"")))

 

[sike11 via OfficeKB.com]

Hi Roger,

Thanks for the help!
I tried this but it gave me an error "#VALUE". Any other ideas?

Regards,

Mary

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.

 

[Pete_UK]

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 future:

=IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT(A1,1)="G",3,"")))

Hope this helps.

Pete

Hi Roger,

Thanks for the help!
I tried this but it gave me an error "#VALUE". Any other ideas?

Regards,

Mary

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.

 

[sike11 via OfficeKB.com]

Hi Pete,

This worked a treat!!!

Thank you very much.

Mary.

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 future:

=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]

 

[Pete_UK]

Thanks for feeding back. In future, please do not multi-post.

Pete

Hi Pete,

This worked a treat!!!

Thank you very much.

Mary.

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 future:

=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.

 

[Roger Govier]

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

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,"")))

 

[sike11 via OfficeKB.com]

Sorry

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.aspx/ms-excel/200701/1

 

[JMay]

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

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 future:

=IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT(A1,1)="G",3,"")))

Hope this helps.

Pete

Hi Roger,

Thanks for the help!
I tried this but it gave me an error "#VALUE". Any other ideas?

Regards,

Mary

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.

 

[James E Middleton]

Looks like this is a reply to a different question...

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

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 future:

=IF(LEFT(A1,1)="B",1,IF(LEFT(A1,2)="AS",2,IF(LEFT(A1,1)="G",3,"")))

Hope this helps.

Pete

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.

 

[Dave Peterson]

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.

Looks like this is a reply to a different question...

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

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 future:

=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.