PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CHECK IF THE TEXT HAS UPPER CASE OR LOWER

INTRODUCTION

Think of a report where we are putting grades against a value say Marks of the students.

Although the grades can be in the same case but recently we saw this situation where the grades were in the upper or lower case.

Another situation can occur when we have to find the case of a letter at a particular position in the Text.

In this article, we would focus on both the case.

1. How to check if the Letter present in the cell is having upper case or lower case, and

2. How to check the case of the letter present at any position in the Text.

CONCEPT OF FINDING THE CASE OF THE LETTER

The concept is very necessary for the innovation to take place.

So, let us understand the concept behind the solution to this problem.

Any character whether its alphabet, big or small, symbols etc. are encoded by the ANSI. It means that all the characters are given the codes which is a number from 0 to 255.

For example, A has a code of 65 and Z has a code of 90.

Similarly a has the code of 97 and z has the code of 122.

Excel has one dedicated function to find out the CODE OF THE CHARACTERS which is known as CODE FUNCTION.

So , the trick is , if we compare the CODE of the given character with the range provided, we can surely know about the case of the Character and proceed further.

CASE 1-IF THE CHARACTER HAS UPPER CASE OR LOWER CASE

Let us take an Example where we have different cells containing the single letter representing the PRIMARY and SECONDARY status by the upper and lower case.

Suppose the capital letter that is upper case shows the primary status and small letter shows the secondary status.

The character shows the group and can be a,b,c,d or e.

CONDITIONS:

If the letter is capital , the status is PRIMARY, if the letter is small , the status is SECONDARY and if any wrong input is there, the status is CHECK THE GRADE.

EXAMPLES:

EXAMPLE	GRADE	EMP ID
1	A	101
2	b	113
3	B	121
4	D	155
5	d	205
6	a	698
7	c	789
8	C	988

STEPS:

Select the cell where you want the result. [Suppose our data is in G7 as shown in the example]
Enter the formula as =IF(AND(CODE(G7)>64,CODE(G7)<91),”PRIMARY”,IF(AND(CODE(G7)>96,CODE(G7)<123),”SECONDARY”,” CHECK THE GRADE”))
The result would appear in the Cell.
Drag down the formula through the column.

EXPLANATION:

Let us try to understand the formula used.

The formula used is =IF(AND(CODE(G7)>64,CODE(G7)<91),”PRIMARY”,IF(AND(CODE(G7)>96,CODE(G7)<123),”SECONDARY”,” CHECK THE GRADE”))

The general construction of the formula is NESTED IF.

We start with the first IF.
The first argument is the condition of the character if it is in UPPER CASE by putting it in the AND FUNCTION and giving it two conditions, the character is greater than 64 and less than 91, which we know is the CODE SEQUENCE FOR ALPHABET IN UPPER CASE.
If this condition is satisfied, the result is PRIMARY otherwise the control goes to the second argument.
The second argument is again a NESTED IF which starts with the AND on the same lines as the first one, and checks if the character is a small letter or not, by checking the CODE VALUE which should be in between 97 and 122.
If this condition is true, the status is SECONDARY.
If this condition is not fulfilled, we pass the value as CHECK THE GRADE which means it is some other character.

CASE 2: CHECK IF ANY CHARACTER AT POSITION N IS IN UPPER CASE OR LOWER

Extending the problem discussed above a bit further, let us find out if a character on certain position is capital or small.

We have already learnt the trick of checking the case of the alphabet.

We’ll solve this problem by joining our CODE FUNCTION with MID FUNCTION.

MID FUNCTION EXTRACTS THE CHARACTER OR TEXT AT A PARTICULAR POSITION IN ANOTHER TEXT FOR A SPECIFIC LENGTH. CLICK ANYWHERE ON THIS LINE TO GET MORE INFORMATION ABOUT MID FUNCTION.

Let us take a few examples and find out the case of the character at the mentioned positions.

EXAMPLE	TEXT	POSITION TO BE CHECKED
1	HELLO	3
2	How	2
3	Are	1
4	yoU	2
5	welcome	6
6	To	1
7	gyanKOSH	3
8	.net	1

CONDITIONS: The text contains capital or small letters.

STEPS:

Select the cell where you want the result. [The first example has the text in G20 and character to be checked in H20]
Enter the formula as =IF(AND(CODE(MID(G20,H20,1))>64,CODE(MID(G20,H20,1))<91),”CAPITAL”,”SMALL”)
The result would appear in the Cell.
Drag down the formula through the column.

EXPLANATION:

Let us try to understand the formula used.

The formula used is =IF(AND(CODE(MID(G20,H20,1))>64,CODE(MID(G20,H20,1))<91),”CAPITAL”,”SMALL”)

We start with the IF FUNCTION .

The first argument is the condition of the character if it is in UPPER CASE by putting it in the AND FUNCTION and giving it two conditions, the character is picked up by the MID FUNCTION and checked if the character is greater than 64 and less than 91, which we know is the CODE SEQUENCE FOR ALPHABET IN UPPER CASE.
If this condition is satisfied, the value returned is CAPITAL .
If this condition is not fulfilled, the output is SMALL as our text has only CAPITAL and SMALL letters.

Contents

CHECK IF THE TEXT HAS UPPER CASE OR LOWER

INTRODUCTION

CONCEPT OF FINDING THE CASE OF THE LETTER

CASE 1-IF THE CHARACTER HAS UPPER CASE OR LOWER CASE

EXPLANATION:

CASE 2: CHECK IF ANY CHARACTER AT POSITION N IS IN UPPER CASE OR LOWER

EXPLANATION:

WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

Like this:

Pin It on Pinterest

KINDLY SPEND FEW SECONDS HERE....